Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

group records and add an incremntal id number per group

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:

customeridproductid
12345623
76543233
12345655
76543234
12345655
7654328
12345655
456788
1234562
45678885
76543255
456783
123456543
45678236

and the resulting data must look like this

rowno()customeridproductid
1456788
245678885
3456783
445678236
112345623
212345655
312345655
412345655
51234562
6123456543
176543233
276543234
37654328
476543255

Thank you all in advance.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you very much.

It is exactly what i wanted.

Cheers!!!