
How can I calculate a cumulative sum in load script?
Jason Michaelides Apr 2, 2012 6:17 PM (in response to data_wrangler)I don't think you can tell QlikView how to join tables directly so you have to get a bit sneaky about it. I think something like the following would work but it's untested...
Tab1:
Load
Key1
,Key2
,Percentile
,etc
;
SQL Select * From Table1;
INNER JOIN (Tab1)
Load
Key1
,Key2
,Percentile AS Percentile_T2
,Metric1
,Metric2
;
SQL Select * From Table2;
This will join all rows where Key1 and Key2 both match.
Now you can group and sum, but judging by your query above you only want to do this where Percentile >= Percentile2, so:
LEFT JOIN (Tab1)
Load
Key1
,Key2
,Percentile
,Sum(Metric1) AS Accum1
,Sum(Metric2) AS Accum2
RESIDENT Tab1
WHERE Percentile >= Percentile2
GROUP BY Key1, Key2, Percentile;
I think that might get you close...
Hope this helps,
Jason

Re: How can I calculate a cumulative sum in load script?
rdelyser Apr 2, 2012 6:19 PM (in response to data_wrangler)Matt,
Load your data into a table (lowest level of granularity).
Then aggregate using a Resident table.
i.e.:
Tbl:
Load index, Cum1, Cum2, ...
From File1.qvd (qvd);
Tbl2:
noconcatenate
Load Index, Sum(Cum1) as Cum1, Sum(Cum2) as Cum2, ...
Resident Tbl group by index;
Drop Table Tbl;
Exit Script;
Unless you are trying to get an incremental aggregation, then use Excel (i.e. b2 = b1 + a2, b3= b2 + a3, etc.)
Regards,

How can I calculate a cumulative sum in load script?
jagan mohan rao appala Apr 3, 2012 1:17 AM (in response to data_wrangler)Hi,
Use this sample script to find Cumulative Sum in script
TableName:
LOAD
F1,
F2,
RangeSum(F2, Peek('CumSum')) AS CumSum;
LOAD * INLINE [
F1, F2
a, 100
b, 200
c, 300
d, 400
];
Hope this helps you.
Regards,
Jagan.

Re: How can I calculate a cumulative sum in load script?
Philip Doyne Sep 1, 2012 7:37 AM (in response to jagan mohan rao appala )Thanks Jagan, A very useful tip. Just in case anyone is interested I have extened this to give me cumulative subtotals based on changes in F1.
Data:
LOAD * INLINE [
F1, F2, F3
a, 1,100
a, 2,200
b, 1,3000
b, 2,4000
c, 1,40000
c, 2,40000
];
TableName:
Result:
NoConcatenate
LOAD
F1,
F2,
F3,
RangeSum(F3, Peek('CumAll')) as CumAll,
if(F1<> previous(F1),F3,RangeSum(F3, Peek('CumF1'))) AS CumF1
RESIDENT Data;
DROP TABLE Data;
I found you need to be careful about the preceeding load so have done it in two passes  It worked for me on a real fife problem.
