Updating Previous or Next Record in the Batch via DataStage Transformer - 1

 It was quite sometime since my last update. Recently I used InfoSphere DataStage Transformer for couple of areas and thought sharing them with others.When we do Data Integration Transformation is always the key and required innovative ways to handle problem.

2 Problems are quite common updating the Next Record in the batch or Previous Record in the batch with some value from current record. In this 2 Series Blog I intend to discuss these 2 cases with the help of InfoSphere DataStage. In Part 1 I will Cover How we change the Next Record. In Part 2 will handle How to change the Previous Record.

Assume we have Data as shown on left hand side. ID is a group where we have different records and end date of Next Record should be Begin Date -1 of Current Record. e.g. End Date of Record 2 should be 19990104 - 1. For explanation purpose I am treating Date as Number. (Off-course you can convert Date Number into valid Date via available functions).

I am not going to discuss basic transformation functions here and also not providing details on looping functions. It is already done @ Looping-in-infosphere-datastage  and other relevant blogs earlier.

This is how DataStage Transformer looks like. To Start with we need to define the Stage Variables. Make Sure Data is sorted on the variable where it is grouped like in this case "ID". I used SaveInputRecord() which is available since Information Server 8.5 to cache set of records based on the group.

 Now we need to add Loop Conditions and required logic based on the Group. I am using GetSavedInputRecord() which saved as part of Stage Variable definition. Now I defined 3 Loop Variables to store Current, Previous and Final State.

Now this is How Output Columns are mapped and Altered Data is processed.

 Based on above logic this is what I receive. End Date is Changed with Begin Date - 1 of Previous Record. It is being done based on Group which is ID here.

I haven't done anything Special. It is same old mechanism we learns in programming days and even with-in DataStage earlier.
SV1= (if SV2= SV3Then 1 Else 0) 
SV2 = current.value
SV3= SV2
Off-course I can get rid of 3rd Stage Variable and made it simple. Reason I used that to have same logic works in both Previous and Next case and hence it is like this. So please go-ahead and improve the logic in your design. This is just an sample and I wanted to keep it consistent.
In Part - 2Continuation of this, will discuss how we can do changes in the Previous Record based on content of Current Record.