Update the next row depending on the values of previous row
Posted: Thu Aug 10, 2006 4:27 am
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
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