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.