Filling gap records

uppalapati2003 · Post by **uppalapati2003** » Wed Mar 19, 2008 7:09 am

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

bkumar103 · Post by **bkumar103** » Wed Mar 19, 2008 7:29 am

Could you please elaborate, what is the calculation for the time gap?

uppalapati2003 · Post by **uppalapati2003** » Wed Mar 19, 2008 7:39 am

bkumar103 wrote:Could you please elaborate, what is the calculation for the time gap?

This depends on the value of the Id,
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

ray.wurlod · Post by **ray.wurlod** » Wed Mar 19, 2008 5:16 pm

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.

Nripendra Chand · Post by **Nripendra Chand** » Wed Mar 19, 2008 9:58 pm

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.

Nripendra Chand · Post by **Nripendra Chand** » Wed Mar 19, 2008 10:01 pm

sorry i forgot to mention all the stage variables with their order:
stgPrevAccnt = stgCurrAccnt

stgCurrAccnt = LN_SRC.Account_Id

stgPrevEndDt = stgCurrEndDt

stgCurrEndDt = LN_SRC.EndDate

stgCurrStartDt= LN_SRC.StartDate