Accumulate calculation in load script

[ronaldwang]

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

[woshua5550 · Accepted solution]

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

[ronaldwang]

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]

Aha ,sounds great

[ronaldwang]

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]

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]

Thanks Dave, it is very nice.

[woshua5550]

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