topic Re: Running Sum on Load Script in QlikView

Running Sum on Load Script

Wed, 09 Oct 2013 17:20:17 GMT

Hi all,

I'm having an issue where i'm not finding an solution for it. The challenge is the following:

I'm loading for 1 qvd each day from the database. (i'm using this approach because to calculate each day on the database is quite an heavy loading.)
I load all the QVD to a table directly which is quiet fast.

The problem resides between those two steps. I needed to create an new measure that would be an running sum of some of those columns.

ex:

Day	value	rum_sum
1	3	3
2	5	8
3	2	10

I've loaded all the qvd's and tried to make one new column with an formula from the resident table but without success.

I'm new to Qlikview so i'm probably missing some part.

Thanks,

Re: Running Sum on Load Script

swuehl — Wed, 09 Oct 2013 17:41:40 GMT

Try using peek() or previous() functions in your load statement:

LOAD

Day,

value,

rangesum(peek(rum_sum),value) as rum_sum

RESIDENT Table;

Re: Running Sum on Load Script

Wed, 09 Oct 2013 18:51:53 GMT

Thanks it did work! For the case i placed above but my issue got a little bit more complex. Is it possible to make this way?

day	country	value	run_sum
1	US	3	3
1	France	4	4
2	US	7	10
2	France	2	6

Re: Running Sum on Load Script

swuehl — Wed, 09 Oct 2013 19:32:58 GMT

Just a little sorting and more checking with peek() function:

DAYS:

LOAD * INLINE [

day, country, value

1, US, 3

1, France, 4

2, US, 7

2, France, 2

];

LOAD *,

if(peek(country)=country,rangesum(peek(run_sum),value),value) as run_sum

Resident DAYS

order by country, day asc;

drop table DAYS;

Re: Running Sum on Load Script

Fri, 11 Oct 2013 08:26:40 GMT

Thanks again swuehl.

Just to conclude after this running sum with partitioning of the variable country i had to create an expression to get the correct value per day independently of where i was drilling.

The expression should be something like this: sum(aggr(max(run_sum),COUNTRY,DAY))