Focal Point
[SOLVED] How to Add and Restart Sequential Numbering

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8957027966

March 20, 2014, 09:59 AM
umun
[SOLVED] How to Add and Restart Sequential Numbering
Hi all,
Please, I am seeking for an assistance on how I can sequentially number records by the number of payments per location. Below is what I am trying to achieve:
  
SN	Location	TYPE 	Desc	PAYMENT		OUTSTANDING 	PAYMENT_DT
1	A	TEST1	TESTA	110.25		111.50		DATE1
2	A	TEST1	TESTA	100.50		 11.00 		DATE2

1	B	TEST2	TESTB	210.25		211.50		DATE1
2	B	TEST2	TESTB	200.50		 11.00		DATE2
3	B	TEST2	TESTB	  6.00		  5.00		DATE3
 
1	C	TEST3	TESTC	10.00		 11.50		DATE1
2	C	TEST3	TESTC	10.00		  1.50		DATE2


Please, can someone help me with how I can add the SN column?

Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
March 20, 2014, 10:22 AM
MichaelBalle
One solution:

TABLE FILE CAR
PRINT
DEALER_COST
RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL

ON TABLE HOLD AS resultTab
END

DEFINE FILE resultTab
COUNTER /I10 = IF COUNTRY EQ LAST COUNTRY THEN COUNTER + 1 ELSE 1;
END
TABLE FILE resultTab
PRINT
DEALER_COST
RETAIL_COST

BY COUNTRY NOPRINT
BY COUNTER
BY COUNTRY
BY CAR
BY MODEL
END



WebFOCUS 7.6, 7.7
Windows, All Output formats
March 20, 2014, 01:02 PM
j.gross
If the numbering depends on only the highest-order sort-field, try using the LIST verb:

TABLE FILE ...
LIST the-dependent-fields
BY highest-order-sort-field
BY additional-sort-fields
ON TABLE HOLD
END

The result will include a numeric datafield, LIST/I5, which meets your requirement.
March 20, 2014, 04:34 PM
njsden
You can add the counter as a COMPUTE field so it will calculated in a single pass *after* sorting has taken place in the internal matrix:

TABLE FILE CAR
PRINT
COMPUTE SN/I11 = IF COUNTRY EQ LAST COUNTRY THEN SN + 1 ELSE 1;
        COUNTRY
        CAR
        MODEL
        DEALER_COST
        RETAIL_COST
BY COUNTRY NOPRINT
END


That will work fine for both FOCUS and non-FOCUS sources (e.g. database tables)

If you have to reset your counter based on more than 1 "key field", say, COUNTRY and CAR, just change your condition slightly:

...
COMPUTE SN/I11 = IF COUNTRY EQ LAST COUNTRY AND CAR EQ LAST CAR THEN SN + 1 ELSE 1;
...




Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
March 21, 2014, 12:42 PM
umun
Thank you.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS