group records and add an incremntal id number per group

[#1]

Hi there,

I think this is an easy one for you guys.

I have a series of data and i want to group them ,during load, by the customer id and add an incremental number id that will begin from one for each group.

The initial data will look like this:

customerid	productid
123456	23
765432	33
123456	55
765432	34
123456	55
765432	8
123456	55
45678	8
123456	2
45678	885
765432	55
45678	3
123456	543
45678	236

and the resulting data must look like this

rowno()	customerid	productid
1	45678	8
2	45678	885
3	45678	3
4	45678	236
1	123456	23
2	123456	55
3	123456	55
4	123456	55
5	123456	2
6	123456	543
1	765432	33
2	765432	34
3	765432	8
4	765432	55

Thank you all in advance.

[Gysbert_Wassenaar · Accepted solution]

Temp:

LOAD *, rowno() as row INLINE [  

    customerid, productid

    123456, 23

    765432, 33

    123456, 55

    765432, 34

    123456, 55

    765432, 8

    123456, 55

    45678, 8

    123456, 2

    45678, 885

    765432, 55

    45678, 3

    123456, 543

    45678, 236

];

Result:

load

          AutoNumber(RecNo(),customerid) as rowno,

          customerid,

          productid

Resident Temp

order by customerid, row;

drop table Temp;

[#3]

Thank you very much.

It is exactly what i wanted.

Cheers!!!