How can I calculate a cumulative sum in load script?
I have a (large) dataset being loaded from text with a composite key and several metric columns. I would like to include a cumulative sum (similar to "full aggregation" in a visualization, but the full aggregation option does not allow me to perform all the calculations and interactions I need).
The general structure of the dataset is:
key1, key2, ... : identifiers for this grouping
percentile : 10 values per key 10, 20, ..., 100
metric1, metric2 : counts of interactions in each percentile block that I would like to aggregate
If I were doing this in sql, I would do it as a self-join -- ex:
, sum(t2.metric1) as cum_sum_metric1
, sum(t2.metric2) as cum_sum_metric2
from table t1
inner join table t2
and t1.percentile >= t2.percentile
group by t1.key1
Is there a way to replicate this behavior in the Qlikview load script? Alternatively, if there is another way to accomplish this end, I am open to other suggestions.