Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ronaldwang
Contributor III

Accumulate calculation in load script

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

 

1 Solution

Accepted Solutions
woshua5550
Contributor III

Re: Accumulate calculation in load script

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

微信截图_20171123124521.png

7 Replies
woshua5550
Contributor III

Re: Accumulate calculation in load script

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

微信截图_20171123124521.png

ronaldwang
Contributor III

Re: Accumulate calculation in load script

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.

woshua5550
Contributor III

Re: Accumulate calculation in load script

Aha ,sounds great

ronaldwang
Contributor III

Re: Accumulate calculation in load script

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

woshua5550
Contributor III

Re: Accumulate calculation in load script

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;

ronaldwang
Contributor III

Re: Accumulate calculation in load script

Thanks Dave, it is very nice.

woshua5550
Contributor III

Re: Accumulate calculation in load script

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