percent_rank() function in Datastage

ray.wurlod · Post by **ray.wurlod** » Tue Mar 11, 2008 5:48 am

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.

chulett · Post by **chulett** » Tue Mar 11, 2008 6:50 am

Silly question, but why not just continue to do that in Oracle?

srini.dw · Post by **srini.dw** » Tue Mar 11, 2008 6:58 am

We do not have Oracle in this project, its Teradata and Datastage. We are converting from Oracle to Teradata

chulett · Post by **chulett** » Tue Mar 11, 2008 7:06 am

Ah... one of those 'gory details' people don't seem to want to burden us with.

Perhaps the question is: is there an equivalent in TeraData?

ArndW · Post by **ArndW** » Tue Mar 11, 2008 7:37 am

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?

srini.dw · Post by **srini.dw** » Tue Mar 11, 2008 11:10 pm

Without two passes you can not calculate percent, rank or any other value that depends on the total count or value.

How to do this, still not able to figure out

ArndW · Post by **ArndW** » Wed Mar 12, 2008 4:29 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Mar 12, 2008 5:22 am

Keep going - you need more than the sum to get rank.

ArndW · Post by **ArndW** » Wed Mar 12, 2008 5:34 am

To complete the design - put in a transform stage that keeps a running total and compute the percentage rank there.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 12, 2008 5:19 pm

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

balajisr · Post by **balajisr** » Wed Mar 12, 2008 11:22 pm

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/

srini.dw · Post by **srini.dw** » Wed Apr 30, 2008 6:07 am

Can you please elaborate more, not able to get any idea..

DATASET--> COPY-----> 2 links>
1 As it is
2 Sort-->Aggr> Join-->1---->Dataset