topic how to accumulate data within the script in QlikView

how to accumulate data within the script

david_ze — Sat, 05 Feb 2011 17:58:03 GMT

Hi,

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

thanks in advanced.

David

how to accumulate data within the script

matt_crowther — Sat, 05 Feb 2011 23:53:34 GMT

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

how to accumulate data within the script

suniljain — Sun, 06 Feb 2011 06:48:25 GMT

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

how to accumulate data within the script

John_Teichman — Mon, 07 Feb 2011 12:07:08 GMT

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

how to accumulate data within the script

david_ze — Tue, 15 Feb 2011 09:17:29 GMT

Thanks for your help !

David

how to accumulate data within the script

suniljain — Tue, 15 Feb 2011 17:12:51 GMT

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

how to accumulate data within the script

david_ze — Tue, 15 Feb 2011 18:50:33 GMT

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

how to accumulate data within the script

prieper — Tue, 15 Feb 2011 21:30:47 GMT

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

how to accumulate data within the script

suniljain — Wed, 16 Feb 2011 09:21:05 GMT

Dear David,

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

Re: how to accumulate data within the script

sujeetsingh — Fri, 27 May 2016 04:34:59 GMT

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