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 ?
History Expiry issue
Moderators: chulett, rschirm, roy
History Expiry issue
Rhaddur
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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
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
Rhaddur
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
Have you considered using a Slowly Changing Dimension 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.