DSXchange

Posted: **Tue Aug 02, 2005 1:39 pm**

Hi,
We are trying to fill in missing rows among three tables.
For Ex: The present scenario

TABLE A                           TABLE B                         TABLE C
ID,Date,Amt                    ID,Date,Amt                        ID,Date,Amt
10000003,1/31/2004,100.00   10000003 1/31/2004,120.00        10000003,1/31/2004,90.00
10000003,2/28/2004,200.00   10000003,2/28/2004,145.00                                                             
                                                             10000003,3/31/2004,130.00
10000003,4/30/2004,110.00                                    10000003,4/30/2004,140.00

After the ETL run

Code: Select all

TABLE A                           TABLE B                         TABLE C
ID,Date,Amt                    ID,Date,Amt                        ID,Date,Amt
10000003,1/31/2004,100.00   10000003 1/31/2004,120.00        10000003,1/31/2004,90.00
10000003,2/28/2004,200.00   10000003,2/28/2004,145.00        10000003,2/28/2004,90.00
10000003,3/31/2004,200.00   10000003,3/31/2004,145.0         10000003,3/31/2004,130.00
10000003,4/30/2004,110.00   10000003,3/31/2004,145.0         10000003,4/30/2004,140.00

For each matching ID the amount for the missing dates has to be copied from the row above, the dates are copied from the other tables when ever missing. If there is a missing row in all the three tables for a specific month we are not concerned about it.
How do we go about doing this in DataStage.
Thanks !

dsrules

Posted: **Tue Aug 02, 2005 3:14 pm**

Hi dsrules,

Could you please correct the format (allignment of your data and column names) of your example?. Its very unreadable now.

Posted: **Tue Aug 02, 2005 8:36 pm**

If you are getting the values from the current row then it should be easy to check for nulls and then fill in the values. If you need values from previous rows then you could do with Stage variables. Do a search. There are lots of posts using stage variables to save previous rows values.

Posted: **Mon Aug 08, 2005 11:50 am**

Thanks Kim. We are using Stage Variables for it !

Posted: **Mon Aug 08, 2005 1:16 pm**

Please post your solution.

DSXchange

Filling Gaps in tables

Filling Gaps in tables