topic Re: How to create a cumulate field loading a table? in QlikView

How to create a cumulate field loading a table?

Wed, 18 Dec 2013 17:14:39 GMT

Hallo you all,

could you help me? I'm facing with the following problem.

I'm able to calculate the B accumulate value in column "B_cumul" using a chart object.

I need to to do the same job, but using the script in the loading stage (create the field B_cumul)

result using chart object:

xls table source:

A B

a	5
b	8
c	3
d	4

scripting:

Directory;

T1:

LOAD

FROM

[..\..\..\elimina\testcum.xlsx]

(ooxml, no labels, table is Foglio1);

Thank you in advance for your time

Re: How to create a cumulate field loading a table?

Gysbert_Wassenaar — Wed, 18 Dec 2013 17:19:11 GMT

Directory;
T1:
LOAD
     A,
     B,
     rangesum(B, peek('C')) as C
FROM
[..\..\..\elimina\testcum.xlsx]
(ooxml, no labels, table is Foglio1);

If you need to have B order in ascending order first and the cumulative calculated using that order then you need to load the excel data first and then use a resident load to reorder the data and calculate the cumulative:

T1:
LOAD
     A,
     B
FROM
[..\..\..\elimina\testcum.xlsx]
(ooxml, no labels, table is Foglio1);

Result:
LOAD *, rangesum(B, peek('C')) as C
RESIDENT T1 
ORDER BY B;

Re: How to create a cumulate field loading a table?

rbecher — Wed, 18 Dec 2013 18:03:27 GMT

Another simple variant:

LOAD A, 
     B,
     Alt(Peek('C'),0) + B as C
FROM
[..\..\..\elimina\testcum.xlsx]
(ooxml, no labels, table is Foglio1)
;

- Ralf

Re: How to create a cumulate field loading a table?

Wed, 18 Dec 2013 23:14:32 GMT

Hallo,

thank you very much, it works perfectly!

The power of the knowledge sharing.

Thanks again