First Stream:
This is for the very last record as it needs to be generated. I sort and hash partition on the company code and pass it via aggregator, group by company code and get the max of date. This will give the last record for each company code. I will then pass it through a transformer where I will hard code the end date to 12/31/9999 and add 1 to EffDt to get the FromDt, and store it in a dataset.
Now we have the last record with the max start date and end date as high 9s.
Second Stream:
Again, I would sort and hash partition on the company code and pass it via transformer where bulk of the logic will be performed using stage variables. Its the classic approach of storing the previous record's key in stage variable and checking with current incoming key, if they are they match that means we are in the same group, if different, start from the begining.
Something like the following should work in your case:
Code: Select all
FrmDt : If in.ComanyCd = PrevKey then DateFromDaysSince(1,PrevEffDt) else StringToDate("01/01/1000", "%mm/%dd/%yyyy")
PrevKey : in.CompanyCd
PrevEffDt : in.EffDt
Store this in another dataset.
Now, use the dataset from the first stream and second stream and combine them using a funnel stage. After combining, sort it again on company code. You will/should have your desired output.
What if there is only one record per company code, whats the logic then?
