Update the next row depending on the values of previous row

[dprasanth]

Hi,
I know that this topic has been discussed few times in this forum, but my problem is a bit different.

I have got four columns in my source database
mno Joined_dt cancel_dt memtype
1 05/05/2005 07/08/2005 A
1 05/09/2005 30/12/1899 B
1 10/11/2005 30/12/1899 C
1 10/02/2006 30/12/2006 C
2 05/01/2006 30/12/1899 A
2 05/02/2006 30/12/1899 B
2 05/03/2006 30/12/1899 A

For each memberno , I have do the following, if the cancel_dt is 30/12/1899, then I have to take the next joined_dt for the member and then subtract one from it and put that in the current cancel_dt column or else leave the cancel_dt as it is
Now for the first row of my example, I will not do anything to the cancel_dt as it is a valid date. But the second row has the cancel_dt of 30/12/1899. So I have to go to the third row and check the joined_dt which is 10/11/20005 , subtract one from it, which will be 09/11/20005 and update this value for the second cancel_dt for memno
So now my columns will look like
mno Joined_dt cancel_dt memtype
1 05/05/2005 07/08/2005 A
1 05/09/2005 09/11/2005 B
1 10/11/2005 09/02/2006 C
1 10/02/2006 30/12/2006 C


I used stage variables do achive this but I am not getting the proper results. The stage variable I am using are
Readln.MEMBERNO | key
Key <> Oldkey | changedkey
Readln.MEMBERNO | Oldkey

I tried using the constraint changedkey =0 and 1 with various permutation combination, but not getting the required rows in my target sequential file.

Could anyone of you please mind guiding me whether this can be achieved.

Thanks a ton in advance.

Regards,
Dprasanth

[ray.wurlod]

Yes it can, and the methods can be found by searching the forum. The technique is to use stage variables to "remember" the values from previous row and to compare the same with the current row.

I think you also need to be doing a similar comparison on MemTyp, based upon your description.

[loveojha2]

Create one more link to the target from the same transformer and send the changed row only after you recieve the new row through this link
and the other link would be sending the row if the row is not to be changed.

Although it is not the exact solution but it should give you the outline of how to proceed with it.

[dprasanth]

Yes it can, and the methods can be found by searching the forum. The technique is to use stage variables to "remember" the values from previous row and to compare the same with the current row.

I think you also need to be doing a similar comparison on MemTyp, based upon your description.

Thanks Ray.. will try to search the forum properly and will try out what you have told me.

[sb_akarmarkar]

Hi,

One way to store old value in hashfile whenever there is cancel_dt= '30/12/1899' Keep key column value as 1 so alway one row in hash file and use some stage variable to store 1 if last record is cancel_dt= '30/12/1899' else 0 and in constraint keep condition like if 1 then update record take value from hash file and cancel_dt = joined_dt-1 (from new row) and insert else insert only....

I think this will help you.

Thanks,
Anupam