DSXchange

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

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

Use a Transformer, partitioning method hash (and sort) with the columns:

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.)

Havoc wrote:

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

Use a Transformer, partitioning method hash (and sort) with the columns:

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,

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.