How to create a new column with accumulate sum in loading script

[tonytang]

Hi, 

I would like to know how can I create a new column with accumulated transaction amount in loading script? I wanna create the table in data model. Please see the below. 

The existing table has three columns, which are Customer Name, Order Date, and Transaction Amount

Now, I want to create a new column called "Accumulated Transaction amount", which is calculated on the Customer Level and  Year(Order date Level). Please see my expected output below

The transaction amount will keep accumulated until the year is changed to new year or the existing customer is changed to next customer name. For example, the first customer as above is accumulated from 4233 to 4275 in 2009, but once the year is updated to 2010, then the accumulated transaction amount will be reset to zero and then it will be accumulated again for every transaction amount in 2010. The value highlighted in red means the accumulated value is reset.

I would like to create this table with 4 columns in the loading script . From my initial thoughts, it may be accomplished with using Autogenerate or Do-loop function, but i am unable to think of a way to resolve it. 

Could everyone can give me some help? I am struggling for a few days.

Thanks so much in advance

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

 

table1:
LOAD 'Customer'&Ceil(Rand()*5)		as CustomerName,
     Dayname(Today()-Rand()*1500)	as OrderDate,
     Ceil(Rand()*1000)				as TransactionAmount
AutoGenerate 100;


table2:
LOAD *,
     RangeSum(TransactionAmount,If(CustomerName=Previous(CustomerName) and Year(OrderDate)=Previous(Year(OrderDate)),Peek(AccumulatedTransactionAmount))) as AccumulatedTransactionAmount
Resident table1
Order By CustomerName, OrderDate;


DROP Table table1;

hope this helps

Marco 

[AshutoshBhumkar · Accepted solution]

Hello,

Maybe below script can help. I have created a sample dataset.

Table:
Load Customer,Date(Date#(Date,'D/MM/YYYY')) as Date, Amount Inline [
Customer,Date,Amount
AB,1/7/2009,4233
AB,7/7/2009,42
AB,27/7/2010,5002
AB,9/11/2010,2337
AB,28/5/2010,14
AH,15/8/2009,460
AH,13/12/2009,13255

];

Table1:
Load * ,
If(Customer = Previous(Customer) and Year(Date)=Previous(Year(Date)),
RangeSum(Peek(Accumulated_Amount), Amount),Amount) as Accumulated_Amount

Resident Table Order by Customer,Date;
Drop Table Table;

Output :

Thanks,
Ashutosh

[tonytang]

Thanks both. It works.

[MarcoWedel]

you're welcome

[AshutoshBhumkar]

Great 🙂