Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have like below:
How do I generate "Sub Seq" which is sequence number grouped by the ID (for each ID). I like it to start from 1 and go till n number of date values that exist for that ID.
ID | Date | Seq | Sub Seq |
1 | D1 | 1 | 1 |
1 | D2 | 2 | 2 |
1 | D3 | 3 | 3 |
2 | D1 | 4 | 1 |
2 | D2 | 5 | 2 |
2 | D3 | 6 | 3 |
2 | D4 | 7 | 4 |
3 | D1 | 8 | 1 |
3 | D2 | 9 | 2 |
A slight issue with your brackets
Change it to
If(IsNull(peek('ID', -1)) or ID <> peek('ID', -1), 1, peek('SubSeq', -1) + 1) as SubSeq
LOAD
ID,
Date,
Seq,
If(IsNull(peek('ID', -1) or ID <> peek('ID', -1), 1, peek('Sub Seq', -1) + 1) as [Sub Seq]
RESIDENT [Your Table]
ORDER BY ID, Seq;
edit: added closing quote around second peek
Did you mean? (Seq)
If(IsNull(peek('ID', -1) or ID <> peek('ID, -1), 1, peek('Seq', -1) + 1) as [Sub Seq]
No, I meant Sub Seq
Sorry not working:
This is what I have:
If(IsNull(peek('ID', -1) or ID <> peek('ID', -1)), 1, peek('SubSeq', -1) + 1) as SubSeq
A slight issue with your brackets
Change it to
If(IsNull(peek('ID', -1)) or ID <> peek('ID', -1), 1, peek('SubSeq', -1) + 1) as SubSeq
Thanks. It worked now.