DSXchange

Hi, I using stage variable to look up a previous row information to compare current row information..

This is what I have in the source:
Order_NB Dept Date
4839722 245 1/2/2007
4860136 260 1/2/2007
4860136 270 1/4/2007
4863059 270 1/2/2007
4863068 270 1/2/2007
4963189 249 1/24/2007
4963189 245 1/24/2007

I need to look at the data row by row and filter out rows that have a previous row and get the new dept, old dept, and the days lag (days difference)

Currently, it is behaving like this:
Order_NB Old Dept New Dept Days lag
4860136 245 260 0
4860136 260 270 2
4863059 270 270 -2
4863068 270 270 0
4963189 270 249 20
4963189 249 245 0

This is my requirement - if no previous similar order_nb available, set old Dept to 0. However, currently it is picking up the previous row regardless of the order_nb

I REALLY want the data to look like this....
Order_NB Old Dept New Dept Days lag
4860136 260 270 2
4963189 249 245 0

My stage Var is written as such --

svDept [trim(DSLink3.MCU_BU) : trim(DSLink3.ORDER_NB)]
svPreviousDept [svDept]
svFinalDept [If (trim(DSLink3.MCU_BU):trim(DSLink3.ORDER_NB)) = svPreviousDept Then 0 Else svDept]

In the transformer, for Old_Dept, I am putting svFinalDept.

Am I not doing the stage variable correctly?

In comparisons the order of stage variables is very important as they get executed in the order they are defined. I believe the order thats going to work for you is
svFinalDept
svPreviousDept
svDept

YES!

DSguru2B wrote:In comparisons the order of stage variables is very important as they get executed in the order they are defined. I believe the order thats going to work for you is
svFinalDept
svPreviousDept
svDept