topic How to add a sub sequence number during data load in App Development

How to add a sub sequence number during data load

rvijayanth — Fri, 15 Mar 2019 15:52:25 GMT

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

Re: How to add a sub sequence number during data load

marcus_malinow — Fri, 15 Mar 2019 16:57:05 GMT

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

Re: How to add a sub sequence number during data load

rvijayanth — Fri, 15 Mar 2019 16:34:10 GMT

Did you mean? (Seq)

If(IsNull(peek('ID', -1) or ID <> peek('ID, -1), 1, peek('Seq', -1) + 1) as [Sub Seq]

Re: How to add a sub sequence number during data load

marcus_malinow — Fri, 15 Mar 2019 16:53:04 GMT

No, I meant Sub Seq

Re: How to add a sub sequence number during data load

rvijayanth — Fri, 15 Mar 2019 17:44:31 GMT

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

Re: How to add a sub sequence number during data load

marcus_malinow — Mon, 18 Mar 2019 16:23:17 GMT

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

Re: How to add a sub sequence number during data load

rvijayanth — Mon, 18 Mar 2019 16:30:02 GMT

Thanks. It worked now.