Filling Gaps in tables

[dsrules]

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

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,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

[pnchowdary]

Hi dsrules,

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

[kduke]

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.

[dsrules]

Thanks Kim. We are using Stage Variables for it !

[kduke]

Please post your solution.