Ranking

[kpsita]

Hi,

I need help in implementing the ranking logic in datastage version 8.0.

In my logic, I need to rank based on three columns.

Example :- column1, column2, column3. The first set will be ranked as 1 and the second set with same value will also be ranked 1 and third set with different value will be ranked 3.

I searched the forum and tried using stage variables, but having issues when I am dealing with set of columns.

Thanks

[chulett]

For doing group change detection on a "set" of columns, I've found the easiest way to do that to concatenate them all into a single delimited field. So rather than check A and B and C you check the unique combination - "A|B|C". Does that make sense? The rest is proper maintenance of the rank in stage variables and knowing by how much to increment it the next time you need to increment it.

[kpsita]

Tahnk you for your reply.

I will concadenate and will follow as suggested. But how do I handle the rank in stage variable. I need to rank third record as '3' if first two records are same.

Thanks

[mhester]

Craig has pretty much set you on a course to handle the logic problem. Now, in my opinion, it is up to you to determine how to best handle it in DS. Think of it in terms of any programming language where you will have variables to hold results as you loop through a set of data. This is what you will do in DataStage too using stage variables.

[Bicchu]

I think using a key-break will help you to acheive your goal.

[Ravi.K]

First Sort the data after concatenation.

Maintain three stage variables and assign default values.

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

Use the svRank at Transformer derivation.

[kpsita]

In the above logic can anybody please tell why should we apply the svRank function in derivation.

The same function was applied as stage variable and I am not geting the exact result as derivation.

Thanks