Dealing with a table

[david_ze]

Hi all,  

I'll appreciate your promptly help on a issue I have:

I have Customer table with Date and Amounts  which looks like this:

Date                      Account               Amount

31/01/2012         101                         1,000

31/01/2012         103                         1,500

28/02/2012         103                         900

31/03/2012         101                         500

30/04/2012         103                         300

30/04/2012         101                         0

See that for account '101' I have no Amount for the date 28/2/2012, and
for account '103' I don't have amount for 31/03/2012.

What I would like to do is to write a script that aggregates the account for each month.

In case that there is no amount for
account on a certain month than we should take the amount from the previous
month.

means that the table should look like this:

31/01/2012         101                         1,000

31/01/2012         103                         1,500

28/02/2012         101                         1,000     added

28/02/2012         103                         900

31/03/2012         101                         500

31/03/2012         103                         900         added

30/04/2012         103                         300

30/04/2012         101                         0

Than to do the aggregation for each month..

01/2012      2,500

02/2012      1,900

03/2012      1,400

04/2012      300

I'll appreciate you'r help on this.

Best

David

[phaneendra_kunc · Accepted solution]

May be this?

Data:

LOAD * INLINE [  

    Date, Account   ,  Amount   

    01/2012,101,       1000   

   01/2012,103,       1500   

   02/2012,103,       900   

    03/2012,101,       500   

    04/2012,103,       300   

    04/2012,101,       0

];

//Add all months to the Data..

Month_Account:

Load distinct Account Resident Data;

left join

Load * Inline [

Date

01/2012

02/2012

03/2012

04/2012

];

Month_Account_NEW:

NoConcatenate

Load * Resident Month_Account;

left join

Load * Resident Data ;

Drop Tables Month_Account,Data;

Final:

NoConcatenate

Load Date,Account,

    IF (Account=PEEK('Account') AND isnull(Amount),PEEK('NEW_VAL'),Amount) AS NEW_VAL

Resident Month_Account_NEW order by Account Desc;

Drop Table Month_Account_NEW;

[david_ze]

Thakns. It works great.

David