Condtions in transformer/Any other options

[sam334]

All,
Need a help with a condition in DS parallel job.

I have a DB2 table, there are three columns as below. Key is ID.

ID, Start_Dt, End_Dt, Status
1,10/22/2012,11/10/2015,In Process
1,10/22/2012,11/10/2016,In Process

We need to update the the row to "Completed" if it finds same record with future END_DT. When we pull the data for reports, we pull In Process one. So, now its incorrectly getting 2 records. but as process is renewed for this ID , we need to make the old one as Completed.

Appreciate your help.

Thanks.

[chulett]

What have you tried?

[sam334]

Craig,
I used a sort stage, sorting on ID and generate a Keychange column. Seems like it marked the Oldest date as 1 and new date as 0. Then used Transformer and put a condition to change the Status column to Completed when Keychange column is 1.

Wat do you think.

[chulett]

Specifically, the Key Change column is set to True (1) when the first record in the "group" is detected, meaning the group has changed. It is False (0) after that as long as it sees the same group value, i.e. it has not changed. So it's not as simple as looking for a "1", I'm afraid.

Perhaps you could use stage variables to store the values of the previous row such that if the next row is for the same group you'll know the previous one needs to be marked as completed and you could send that out an update link. This would work even if you had several that needed their STATUS changed in a row...

[rkashyap]

We need to update the the row to "Completed" if it finds same record with future END_DT.

What are the requirements ... if multiple records with same key but past END_DT exist?

[sam334]

ok Thanks Craig. Will try it now.

@rkashyap,
If multiple records in the same key then change the status column of the past end date column to 'Completed;

We can pull only duplicates rows from database can create this job as an Update only.