this is the example
My source is DB2
No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31
Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same
The target data should be like this: target also db2
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 2007-11-22
1 aaa icici N 2007-11-18 2007-11-19
if any body have the idea please reply back asap
thanks
how i find the end date based on the previus record eff_date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
Re: how i find the end date based on the previus record eff_
Use a Transformer, partitioning method hash (and sort) with the columns:uppalapati2003 wrote:this is the example
My source is DB2
No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31
Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same
The target data should be like this: target also db2
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 2007-11-22
1 aaa icici N 2007-11-18 2007-11-19
if any body have the idea please reply back asap
thanks
No.
Eff_dt
Sort - No. Ascending, Eff_Dt Ascending
Use a Stage variable to set the EFF_DT of the current row and use that stage variable to set the End_Date for the next record.
Build the logic in the transformer to check for the switch of the key column (No.)
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
Re: how i find the end date based on the previus record eff_
Havoc wrote:Use a Transformer, partitioning method hash (and sort) with the columns:uppalapati2003 wrote:this is the example
My source is DB2
No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31
Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same
The target data should be like this: target also db2
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 2007-11-22
1 aaa icici N 2007-11-18 2007-11-19
if any body have the idea please reply back asap
thanks
No.
Eff_dt
Sort - No. Ascending, Eff_Dt Ascending
Use a Stage variable to set the EFF_DT of the current row and use that stage variable to set the End_Date for the next record.
Build the logic in the transformer to check for the switch of the key column (No.)
first of all thanks for u r response
however how i set the End_Date for the next record.
if suppose my input data like
EFFective enddate
1 aaa 2007-11-13 9999-12-31
1 aaa 20007-11-03 999-12-31
2 bbb 2007-11-02 9999-12-31
2 bbb 20007-10-23 9999-12-31
the output should be
1 aaa 2007-11-13 9999-12-31
1 aaa 2007-11-03 2007-11-13
2 bbb 2007-11-02 9999-12-31
2 bbb 2007-10-23 2007-11-02
can u give any suggeestions
Thanks,
Srini
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You use two stage variables to remember the value from the previous row and to detect when it has changed. Search the forum for detailed explanation of the technique.
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.