DSXchange

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

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

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.

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

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

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 ...."

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 .

Guys, any help on this

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?

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.

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.

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"

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.