Load script : pb to create table with cumulated amount

[sdavinblanc]

Hello,

I'm facing a problem to create a table with cumulated amount from two tables.

I have a table BUSINESS_PERIOD with two colums :

- Business_code (Key)

- Period (year month format : yyyymm).

In this table you can find all period from the start of the business to the end.

I have a second table BUSINESS_ORDER with three columns :

- Business_code (Key)

- Period (year month format : yyyymm).

- Amount

In this table you can find only period where there is an amount associated.

I need to create a final table with three columns : 

- Business_code (Key)

- Period (year month format : yyyymm).

- Amount

In this final table all period of each business need to be present and the amount is cumulated each month with previous months for each business.

You can find a screenshot of the tables with data in order to understand better.

I can't find how to do it... 

I someone has an idea to help ! thank you 

[marcus_sommer]

Try:

t1: load * from BUSINESS_PERIOD; left join(t1) load * from BUSINESS_ORDER;

t2: load *,
             if(Business_code = previous(Business_code),
             peek('KumAmount') + Amount, Amount) as KumAmount
resident t1 order by Business_code, Period;

drop tables t1;

[sdavinblanc]

It's not working...I have no amount in the KumAmount

[marcus_sommer]

Are all records with the periods + codes + amounts there - means the join as first step is working?

[sdavinblanc]

Yes the join is working before using the previous and peek syntax.

[marcus_sommer · Accepted solution]

Add a small adjustment to treat NULL as ZERO:

t2: load *,
             if(Business_code = previous(Business_code),
             rangesum(peek('KumAmount'), coalesce(Amount, 0)),
             coalesce(Amount, 0)) as KumAmount
resident t1 order by Business_code, Period;

[sdavinblanc]

It's working fine, thank you !