How to get Ranking using datastage

[mekrreddy]

Is there any way to assign rank?

Lets say there are 4 students with input col as Name and Marks


A 59

B 46

C 46

D 52


Can any one please explain the ranking process for above data. Output should be name and rank

A 1

B 2

C 2

D 4


Thank you.

[chulett]

Sort desc by the field you want to rank by then use stage variables for the rank. The trick would be to not increment rank for ties and then keep track of how many ties there were for the next increment.

[mekrreddy]

thank you for the reply, can you please explain the logic little more on the above

[chulett]

Your rank variable would only be incremented when the Marks value changes and ties mean you need to 'skip' ranks. So two values of 46 means the rank goes up by 2 next time rather than 1, so simply keep track of the number of Names per Mark and use that as the increment when the Mark changes. All that can be tracked / managed in stage variables.

[DSguru2B]

First you need to run your job on single node as you need to compare current row by previous row to keep track of duplicate rows.
Then as Craig advised, keep track of this in stage variables of when to increment by 1 and when to increment by 2.
Search the forum threads for "row comparisons" to find out how to use stage variables to compare current row to previous rows.
Once you get the hang of that, then its just a matter of a few more stage variables with if then else statements and increments for the rank.