topic Re: Accumulate calculation in load script in QlikView

Accumulate calculation in load script

ronaldwang — Wed, 25 Nov 2020 16:16:04 GMT

I have a table as in T1 and I have loaded the data in such order already, eg, sorted by first column then second column. Now I want to use column 3 to get a calculated column 4 where column is accumulated sum of column 3(base on condition in column1&2). And the result is as in T2. Can anyone share some thought on this . thanks

Re: Accumulate calculation in load script

woshua5550 — Thu, 23 Nov 2017 04:50:44 GMT

Hi Ronal

I figured it out ,plz check my test file attached.

plz notice that there might be something wrong with your "T2" , accumulation break here

Re: Accumulate calculation in load script

ronaldwang — Thu, 23 Nov 2017 05:03:41 GMT

Hi Dave,

That is very nice. the T2 is correct, as they are different companies. so I just need to modify your code to left join with one more dimension.

Re: Accumulate calculation in load script

woshua5550 — Thu, 23 Nov 2017 05:47:14 GMT

Aha ,sounds great

Re: Accumulate calculation in load script

ronaldwang — Thu, 23 Nov 2017 06:01:17 GMT

Hi Dave, the script works well while there is only single company, but won't work while the number of companies increase. any thought?

Re: Accumulate calculation in load script

woshua5550 — Thu, 23 Nov 2017 06:47:45 GMT

then we need to change the code into this

Data:
LOAD RowNo() as ID,
Company,
Date,
Dividend_Paid
FROM
T1.xls
(biff, embedded labels, table is Sheet1$);

TMP:
LOAD Company,
ID,
Dividend_Paid
Resident Data;
Left Join
LOAD
Company,
ID as ID2,
ID - IterNo() + 1 as ID
Resident TMP
While IterNo() <= ID;

Left Join (Data)
LOAD Company,ID2 as ID,IF(Sum(Dividend_Paid)=0,'-',Sum(Dividend_Paid)) as Dividend_Paid_2014 Resident TMP Group by Company,ID2;

DROP Table TMP;

Re: Accumulate calculation in load script

ronaldwang — Fri, 24 Nov 2017 00:54:19 GMT

Thanks Dave, it is very nice.

Re: Accumulate calculation in load script

woshua5550 — Fri, 24 Nov 2017 04:13:37 GMT

could you please kindly mark my reply as "Helpful" , I want to achieve a mission , thanks !