Sequence Generator by partition?


(Danny Ryan) #1

Hi,
Does anyone know how to create a sequence that is by a partition?

If my data looked like the following;
Col_1, Col_2, Col_3
1, A, 2017-12-01
1, A, 2017-12-27
1, A, 2018-03-27
1, B, 2017-12-02
1, B, 2017-12-28
1, B, 2018-03-29

Id like to generate a sequence number per partition, where the partition is col_1 + col_2 and ordered by col_3 asc.

So the results would look like;
Col_1, Col_2, Col_3, pur_seq
1, A, 2017-12-01, 1
1, A, 2017-12-27, 2
1, A, 2018-03-27, 3
1, B, 2017-12-02, 1
1, B, 2017-12-28, 2
1, B, 2018-03-29, 3

Any help would be greatly appreciated.
Thanks
Danny


(Danny Ryan) #2

Nevermind, sorted it using the representative creator.
I placed the columns in my partition into the group by tab.
Then created one instance rule to return the columns i wanted plus the group.rownum
And finally add the columns i wished to order b in the selection rules.


#3

Hi Danny,

Other solution to your case would be to use namedSequence function which has a name of the sequence as its first argument, so you can put there something like this:

namedSequence(col_1+'|'+col_2)

The only obstacle is that first argument should be a string constant, the workaround here is to use Dynamic Expression Assigner step, instead of Column Assigner step.