how to accumulate data within the script - Qlik Community

david_ze · ‎2011-02-05

Hi,

I'd like to accumulate date during the reload do you have a sample script for this ?

thanks in advanced.

David

matt_crowther · ‎2011-02-05

To aggregate data in the load there are several ways. With each method the key points are a the aggregator - obviously (sum(Sales), count(Customers)) and the group you want to aggregate across.

The most simple way to begin with script aggregation is to take a simple dataset: let's say daily sales - multiple products and product groups sold over different days. A natural requirement would be to aggregate the daily sales:

Load the dataset as normal then perform a resident load against it with the aggregation expression (sum(Sales))and the Date after the Resident simply add a Group By clause:

DailySales:

Load only(Date),

Sum(Sales)

Resident SalesData Group By Date;

That should do it.

You can also do the resident load and then left join back to the original data.

Hope that helps,

Matt - Visual Analytics Ltd

suniljain · ‎2011-02-06

Can you post limited data in Excel and what types of accumulation of you want ?.

John_Teichman · ‎2011-02-07

This example shows how to calculate a running total (a type of accumulation) in the load script.

Regards,
John Teichman

david_ze · ‎2011-02-15

Thanks for your help !

David

suniljain · ‎2011-02-15

Dear David,

can you tell me on which dimention you want to accumulate data?. Because in different different scenario we use different method of accumulation.

Regards

Sunil

david_ze · ‎2011-02-15

hi Sunil,

see an example of the data:

Date	installs	accumulated

01/01/2011	5	5
02/01/2011	4	9
03/01/2011	2	11
04/01/2011	3	14
05/01/2011	2	16
06/01/2011	1	17
07/01/2011	8	25
08/01/2011	5	30
09/01/2011	3	33

I need to creat a new field 'accumulated' (during the reload).

best

David

prieper · ‎2011-02-15

Should work as fllws:

Data:
 LOAD
 Date,
 NUMSUM(installs, PEEK('installs', -1)) AS installs;
 LOAD * INLINE [Date , installs , accumulated
 01/01/2011 , 5 , 5
 02/01/2011 , 4 , 9
 03/01/2011 , 2 , 11
 04/01/2011 , 3 , 14
 05/01/2011 , 2 , 16
 06/01/2011 , 1 , 17
 07/01/2011 , 8 , 25
 08/01/2011 , 5 , 30
 09/01/2011 , 3 , 33];

HTH
Peter

suniljain · ‎2011-02-16

Dear David,

peter's suggestion is perfect and if you any query reply.

sujeetsingh · ‎2016-05-27

Seems David just flied to NASA with the solution provided by PETERE‌. He should have marked this thread answered. Awesome Peter.