Need logic for Rank generation

bicap · Post by **bicap** » Wed Dec 17, 2014 10:41 pm

Source sample data
===============================
PRDNAME PRDAMT
PD1 100
PD1 120
PD1 130
PD2 200
PD2 210
PD3 300
PD3 200

Hi All,
I need your help in getting datastage logic for above scenario ..

Iam having product and amount's respectively . I just want to generate rank for highest PRDAMT in same group.

In above example there are three different (PD1,PD2,PD3) products.

Expected Ouput
=====================
PRDNAME PRDAMT PDHIGH
PD1 100 3
PD1 120 2
PD1 130 1
PD2 200 1
PD2 210 2
PD3 300 1
PD3 200 2

jerome_rajan · Post by **jerome_rajan** » Thu Dec 18, 2014 12:26 am

Do you HAVE to use DataStage? What is the source - Table or file?

ArndW · Post by **ArndW** » Thu Dec 18, 2014 12:54 am

Sort your data by ascending product and descending amount. Do a key change on product in the sort stage as well. Then you can use a stage variable in a subsequent transform stage to count up rank and output that value, restarting at 1 when a key change occurs.

bicap · Post by **bicap** » Thu Dec 18, 2014 1:04 am

I need to apply logic in datastage . My source is file

bicap · Post by **bicap** » Tue Dec 23, 2014 4:18 am

I sorted the data on ascending product(PRDNAME) and descending amount. In this case Keychange column is always is giving as 1.

Please help me on this logic

vinothkumar · Post by **vinothkumar** » Tue Dec 23, 2014 4:49 am

You need to implement 2nd step which Ray mentioned..
"Then you can use a stage variable in a subsequent transform stage to count up rank and ...."

bicap · Post by **bicap** » Tue Dec 23, 2014 5:00 am

For different products (PD1,PD2,PD3) the rank to be generated .
If i sorted the data on ascending product(PRDNAME) and descending amount. Then keychange will always give '1' .Then how can i generate rank for other group .

For each different product rank should start from 1 .

bicap · Post by **bicap** » Tue Dec 23, 2014 6:30 am

Guys, any help on this

priyadarshikunal · Post by **priyadarshikunal** » Tue Dec 23, 2014 7:12 am

What help do you require apart from the solutions already provided by other posters earlier in this post? I think the solution provided should work just fine. Did you try implementing it?

bicap · Post by **bicap** » Tue Dec 23, 2014 7:17 am

I sorted the data by ascending product and descending amount.

Plesae tell me how to do ( Do a key change on product in the sort stage )as well.

priyadarshikunal · Post by **priyadarshikunal** » Tue Dec 23, 2014 7:47 am

the rank operation needs to be performed in transformer with the use of stage variables. The key change column will just help you to identify when to reset that counter so that you start fresh from 1. If you look the output of sort stage you will have 1 and 0 which is the indicator whether the dept has changed or not. Even if you do not have key change column the same can be checked in the stage variable where you will need to store previous records dept.

The fact that stage variables are executed from top to bottom and do store the value unless overwritten by another value, will help to derive the solution.

bicap · Post by **bicap** » Sun Feb 01, 2015 10:10 pm

Hi All,

I sorted the data on ascending product(PRDNAME) and descending amount and Keychange column is always is giving as 1.

Sort stage
---------------------
Key =PRDNAME
Sort order = Acsending
Key=PRDAMT
Sort order= Desending

Hash Partition = PRDNAME

PLease help me whethere iam missing anything to generate keychange value other than 1

Also how to do "Do a key change on product in the sort stage as well"

ray.wurlod · Post by **ray.wurlod** » Mon Feb 02, 2015 1:45 am

Use two Sort stages. In the first generate the Key Change column. In the second, mark the first sort key as not to be sorted because it's already sorted.