Need logic for Rank generation
Moderators: chulett, rschirm, roy
Need logic for Rank generation
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
===============================
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
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Do you HAVE to use DataStage? What is the source - Table or file?
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
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"
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"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: