In my previous Update I mentioned how we can update Next Record in the Batch with value of current one. Or to re-iterate updating the current record with previous value to keep it simple.
Now in this one using Same Example I explain How we can do the reverse, use the Current Record to update the Previous One. Quite common in the field and we tend to use for joins, multiple jobs or creating temp data-set/table and do it. Here is how we can achieve it with InfoSphere DataStage.
Below is the Input Data.
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". As I need to change the Previous Row, Easiest option is sort it "Descending" based on that field like in my case BeginDate. I used SaveInputRecord()
which is available since Information Server 8.5 to cache set of records
based on the group. No Change in this piece as we still need to store the records to achieve changes. Hence caching piece of DataStage comes handy.
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 of Previous Record is Changed with Begin Date - 1 of Current Record. It is being done based on Group which is ID here. Don't get confused, Records shown are Reverse Sorted.
If you look at Data Closely It is like this and is what we need. e.g. My 2nd Record Start Date is 20000512 and EndDate is -1 of this record i.e. 20000511. Off-course I used Integer Field but you can decide to use any including various data conversions.
Again here also I used same mechanism understood long back in college of using temp variables. This is how we can do in DataStage via
SV1 = current.value
SV2= (if SV1= SV3Then 1 Else 0)
SV3= SV1
And it is easiest and quickest way of achieving this and should be easy to maintain. Off-course it can be extended into multiple other scenarios as well.
Soon will provide more updates into relevant areas including upcoming Information Server 9.1.