how to find the enddate

uppalapati2003 · Post by **uppalapati2003** » Fri Nov 30, 2007 6:36 am

No1 no2 no3 eff_date end_date
1 1 1 2007-11-20 9999-12-31
1 1 1 2007-11-17 2007-11-18
1 1 1 2007-11-12 2007-11-15

This is the my sample source data..what i need is whenever the combination of the (no1,no2,no3) of the first record and the combination of the second is same the end date of the previous record is the effective record -1

target data like this

No1 no2 no3 eff_date end_date
1 1 1 2007-11-20 9999-12-31
1 1 1 2007-11-17 2007-11-19
1 1 1 2007-11-12 2007-11-16 .

if any body have any idea please let me know that was very helpful to me

ray.wurlod · Post by **ray.wurlod** » Fri Nov 30, 2007 1:38 pm

Use a Sort stage to sort by the three keys and the generate a key change column. Use a Transformer stage, with stage variables to "remember" the value of the date from the previous row. Reset on key change.