Page 2 of 2
Posted: Thu Mar 08, 2007 3:00 pm
by DSguru2B
It doesnt matter. The rule is
For subsequent rows, if the first three columns are same and the fifth column is different, only then increment, else 1.
DId you try coding as I suggested.
Posted: Thu Mar 08, 2007 3:49 pm
by Dsnew
DSguru2B wrote:Well you will have to do a column by column check then. Compare column 1, if they are same, check scond column, if same check third, if same, check the 5th column.
Now if the 5th column is same, refresh the rank to 1, if its different, increment the rank.
If any of the first three columns are different, start from 1 again.
Makes sense ???
Did not make much sense to me, have not done anything like that before.
Can you please provide more details..
Posted: Thu Mar 08, 2007 4:11 pm
by DSguru2B
Three stage variables,
PrevThree, PrevSales and
cond. Their derivations will be
Code: Select all
cond | if @INROWNUM =1 then 1 else if (in.STATE:in.CITY:in.ZIP=
PrevThree) AND (in.SALES <> PrevSales) then cond +1 else 1
PrevThree | in.STATE:in.CITY:in.ZIP
PrevSales | in.SALES
The stage variables should be in the same order. The stage variable
cond will have your Rank.
Posted: Thu Mar 08, 2007 4:43 pm
by narasimha
You can do it in Oracle
Assuming your table name is Car.
Code: Select all
SELECT state, city, zip, sales_number,
dense_rank() OVER (PARTITION BY state, city, zip ORDER BY sales_number DESC) rank
FROM Car
ORDER BY state, city, zip, sales_number DESC
Simple!
Posted: Thu Mar 08, 2007 4:59 pm
by ganesh123
DSGuru2B can you show it using RowProcCompareWithPreviousValue
I am doing the following but not getting :
Code: Select all
1)current=read.ST:read.CITY:read.ZIP
2)sv1=RowProcCompareWithPreviousValue(current)
3)sv2=if sv1 then RowProcCompareWithPreviousValue(read.SALES) else Rank=1
4)Rank=if sv2 then sv2 else Rank+1
Posted: Thu Mar 08, 2007 6:24 pm
by ray.wurlod
My guess is that you need the Transformer stage downstream of an Aggregator stage.
And, of course, data pre-sorted on the aggregation (grouping) columns.
Posted: Thu Mar 08, 2007 7:27 pm
by DSguru2B
You dont need to use the RowProcCompareWithPreviousValue for more than one time, I think. Just use the code I gave you. It will give you the Rank as you want. Its tested code.
Posted: Fri Mar 09, 2007 12:04 am
by Dsnew
narasimha wrote:You can do it in Oracle
Assuming your table name is Car.
Code: Select all
SELECT state, city, zip, sales_number,
dense_rank() OVER (PARTITION BY state, city, zip ORDER BY sales_number DESC) rank
FROM Car
ORDER BY state, city, zip, sales_number DESC
Simple!
The query works great! Thanks narasimha.
I have one way to do it now.
DSGuru2b, I still having problem getting your code to work. I am getting all Ranks as 1
Posted: Fri Mar 09, 2007 7:38 am
by DSguru2B
Did you do exactly what I asked you to do? Sorting, order of stage variables etc. As I said, thats tested code. If it works for me, it will work for you. Go through my post again and do exactly as I said. Can't help you more then that.
Posted: Fri Mar 09, 2007 4:24 pm
by Dsnew
DSguru2B,
I rechecked it, it works fine for everything except when the sales_number is the same within a group of state, city and metro.
In such conditions we get a 1 instead of the getting the same rank again, because of the condition
(in.SALES <> PrevSales) then cond +1 else 1.
Therefore if in.SALES = PrevSales you will get a 1
Appreciate your help
Posted: Fri Mar 09, 2007 5:54 pm
by DSguru2B
Ok, I thought it was to be reset to 1. My bad. Use the following
Code: Select all
cond | if @INROWNUM =1 then 1 else if (in.STATE:in.CITY:in.ZIP=
PrevThree) AND (in.SALES <> PrevSales) then cond +1 else if
(in.STATE:in.CITY:in.ZIP= PrevThree) AND (in.SALES =
PrevSales) then cond else 1
PrevThree | in.STATE:in.CITY:in.ZIP
PrevSales | in.SALES
Posted: Fri Mar 09, 2007 8:31 pm
by Dsnew
Kool! Works like a charm!
I have both ways now.
You guys rock
