Generate a sequence based on Alphabetical order:

[times29]

Hi,
i have data as below

CODE
421
BD
AA
AA
CD
KK
MM

i Want to create sequence based on Alphabetical order

CODE seq

421 1
AA 2
AA 2
BD 3
CD 4
KK 5
MM 6


Thanks

[chulett]

That's a standard group change detection task. Use stage variables in a transformer, one to track the previous group and one to increment your sequence when it changes. Use a Sort stage before it with a key change column in order to simplify the transformer work. In that case you would only need a single stage variable.

[times29]

Will this work

svCurrent=InputCode
svRank--> if svCurrent=svPrevious then svRank else svRank+1
svPrevious=InputCode

[chulett]

If you have sorted the data but not made use of the KeyChange feature, then sure. But you don't really need the first stage variable:

Code: Select all

svRank--> if InputCode=svPrevious then svRank else svRank+1 
svPrevious=InputCode

Using all three won't hurt and may make it a teeny bit easier to read. With the KeyChange coming in from a Sort stage, you can use a single stage variable:

Code: Select all

svRank--> if KeyChange = 0 then svRank else svRank+1

Of course, you need to worry about your partitioning to make sure everything with the same code arrives together, assuming a multiple node config. And make sure you set the Initial Value of svRank to zero.