Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ronaldwang
Creator III
Creator 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
Creator III
Creator III

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

View solution in original post

7 Replies
woshua5550
Creator III
Creator III

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
Creator III
Creator III
Author

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
Creator III
Creator III

Aha ,sounds great

ronaldwang
Creator III
Creator III
Author

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
Creator III
Creator III

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
Creator III
Creator III
Author

Thanks Dave, it is very nice.

woshua5550
Creator III
Creator III

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