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>,

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


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.

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;
...

Thank you.