History Expiry issue

[rhaddur]

History Expiry issue

Hi All

My input is sequtial file and the data is like this.

SK/natural key , Effective Date , Balance
20031 2015-09-07 1.33
20031 2015-09-08 4.2
20031 2015-09-09 6.9





I would like to have my target in the following table Assume History ( Composit Primary Key is 1) SK/natural key , 2) Record Arrival Date)


SK/natural key, Record Arrival Date, Record_Timestanp , Effective Date, Record End Date, Balance
20031 2015-09-07 CurrentTimestamp 2015-09-07 2015-09-08 1.33
20031 2015-09-08 CurrentTimestamp 2015-09-08 2015-09-09 4.2
20031 2015-09-09 CurrentTimestamp 2015-09-09 9999-12-31 6.9




1) Where I am assigning Effective DT = Record Arrival Date
2) When I recieve same SK/natural key in new reocrd Latest(different) "Effective Date" then
I need to Expire previous "Record End Date" with New record's "Effective Date"
3) At any time lastest record will have end date 9999-12-31

Can Any body Suggest solution for this issue ?

[ray.wurlod]

Probably the best solution is for you to create a DataStage job to effect the transformation that you have partially specified. Even better would be to write out a complete set of mappings first.

[rhaddur]

Hi Ray,

It is not an issue to achieve if my source and Target is DataBase, which will be easy to identify the existing record to expiry it .This Requriement is more or less equivalent SCD type2 ,

Main issue.
My source is Flat file and target is DataSet - so I am not sure how to find the existing record for expiry purpose.

Is there any way tha I am able to find existing recrods in this process



If I go for the job desin

ScrFile ------Transormer------DataSet

[ray.wurlod]

No, you will need a bit more complexity than that, perhaps a lookup against the target using the natural key.

Have you considered using a Slowly Changing Dimension stage?