Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to give sequence to the value of ID. Grouped by ID, for every Value i would like to give a sequence number - 1,2,3
No specific order. Just a sequence number for every instance grouped by ID.
Data:
ID | Value |
1 | P0132-9540 |
1 | P8594-123 |
1 | P213098-323 |
2 | P-9540 |
2 | P-123 |
2 | P-323 |
O/P:
ID | Value | Sequence |
1 | P0132-9540 | 1 |
1 | P8594-123 | 2 |
1 | P213098-323 | 3 |
2 | P32-9540 | 1 |
2 | P94-123 | 2 |
2 | P98-323 | 3 |
Any help is highly appreciated.
Assuming you're loading from your real data source in ID order:
LOAD *
,if(ID=previous(ID),peek(Seq)+1,1) as Seq
Assuming you're loading from your real data source in ID order:
LOAD *
,if(ID=previous(ID),peek(Seq)+1,1) as Seq
That was perfect John!!
Thanks:)
May be
LOAD *,Autonumber(RowNo(),ID) as Sequence
Regards,
Antonio
Oooh, even better! Simpler, and not sensitive to the order of the data!