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

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

tonytang — Sat, 13 Nov 2021 17:32:30 GMT

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

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

MarcoWedel — Sun, 14 Nov 2021 12:35:28 GMT

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

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

AshutoshBhumkar — Sun, 14 Nov 2021 12:47:57 GMT

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

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

tonytang — Sun, 14 Nov 2021 16:16:39 GMT

Thanks both. It works.

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

MarcoWedel — Mon, 15 Nov 2021 20:37:00 GMT

you're welcome

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

AshutoshBhumkar — Tue, 16 Nov 2021 05:34:33 GMT

Great 🙂