percent_rank() function in Datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Rank - and any derivative of it - is a display function, not a calculated function.
DataStage only takes one pass through the data, unless you explicitly design it to take two. Without two passes you can not calculate percent, rank or any other value that depends on the total count or value.
DataStage only takes one pass through the data, unless you explicitly design it to take two. Without two passes you can not calculate percent, rank or any other value that depends on the total count or value.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
If Teradata sql doesn't allow this function you could try running the data through an aggregator to get the total, then use that result to compute the rank?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
No need for 2 passes.
1. Read the data, split the stream into two links, A and B
2. Link A does a aggregation on a dummy column to produce one row with the sum.
3. Link B does an inner join to the aggregator output of A
1. Read the data, split the stream into two links, A and B
2. Link A does a aggregation on a dummy column to produce one row with the sum.
3. Link B does an inner join to the aggregator output of A
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To complete the design - put in a transform stage that keeps a running total and compute the percentage rank there.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
At some point you are going to need to SORT the data (at least for the second pass) to get a rank.
Then you need to implement your business rule for tied ranks. For example 1,2,2,4,...
What Arnd called "percentage rank" is simply "percentage".
Then you need to implement your business rule for tied ranks. For example 1,2,2,4,...
What Arnd called "percentage rank" is simply "percentage".
Last edited by ray.wurlod on Wed Mar 12, 2008 11:48 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
As Craig pointed out why not you do this in teradata itself when it is available?
Check out this URL.
http://readvitamin.com/2007/08/22/analy ... -teradata/
Check out this URL.
http://readvitamin.com/2007/08/22/analy ... -teradata/