Hi all,
I have the sample data given below., for table based on Key(Id,Eff_D)
Id Eff_D Expr_D
1 2007-12-1 9999-12-31
2 2006-04-13 2006-07-14
2 2006-10-10 9999-12-31
The first task would be to find if time gap exists for records of same Id, and next would be to fill the time gap with a gap record.
The gap record to be inserted into the table will be,
2 2006-07-15 2006-10-09
Filling gap records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
This depends on the value of the Id,bkumar103 wrote:Could you please elaborate, what is the calculation for the time gap?
i have to compare the EXPR_D value of the current record with the EFF_D value of the previous record, if they are not equal, then there is a difference in time gap., for which i should insert the gap records.
Thanks
Srini
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That should work. You can use date arithmetic to get the two required dates where the key is the same. Set a flag in that case, use that flag to output the rows to a separate output, and bring them all together with a downstream Funnel stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
I followed the below steps:
1. partition on 'id'
2. sort on 'id' and 'start date (Ascending order)'.
3. use stage variables to see the repetition of id and check whether end date of previous record and start date of current record are contiguous or not.
4. Use two output link in this transformer stage and use following derivation:
First output Link:
if stgPrevAccnt = stgCurrAccnt and stgCurrStartDt <> DateFromDaysSince(1, stgPrevEndDt) then LN_SRC.Account_Id:",": DateFromDaysSince(1, stgPrevEndDt):",": DateFromDaysSince(-1, stgCurrStartDt) else LN_SRC.Account_Id:",": LN_SRC.StartDate:",": LN_SRC.EndDate
Second Output Link:
LN_SRC.Account_Id:",": LN_SRC.StartDate:",": LN_SRC.EndDate
Here second output link should have following constraint
stgPrevAccnt = stgCurrAccnt and stgCurrStartDt <> DateFromDaysSince(1, stgPrevEndDt)
then funnel both the links and you will get the records with gap filled.
1. partition on 'id'
2. sort on 'id' and 'start date (Ascending order)'.
3. use stage variables to see the repetition of id and check whether end date of previous record and start date of current record are contiguous or not.
4. Use two output link in this transformer stage and use following derivation:
First output Link:
if stgPrevAccnt = stgCurrAccnt and stgCurrStartDt <> DateFromDaysSince(1, stgPrevEndDt) then LN_SRC.Account_Id:",": DateFromDaysSince(1, stgPrevEndDt):",": DateFromDaysSince(-1, stgCurrStartDt) else LN_SRC.Account_Id:",": LN_SRC.StartDate:",": LN_SRC.EndDate
Second Output Link:
LN_SRC.Account_Id:",": LN_SRC.StartDate:",": LN_SRC.EndDate
Here second output link should have following constraint
stgPrevAccnt = stgCurrAccnt and stgCurrStartDt <> DateFromDaysSince(1, stgPrevEndDt)
then funnel both the links and you will get the records with gap filled.
-Nripendra Chand
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)