Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to create a cumulate field loading a table?

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

a5
b8
c3
d4

scripting:

Directory;

T1:

LOAD

     A,

     B

FROM

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

(ooxml, no labels, table is Foglio1);

Thank you in advance for your time

1 Solution

Accepted Solutions

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

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;


talk is cheap, supply exceeds demand
3 Replies

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

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;


talk is cheap, supply exceeds demand
MVP
MVP

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

Another simple variant:

LOAD A,

     B,

     Alt(Peek('C'),0) + B as C

FROM

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

(ooxml, no labels, table is Foglio1)

;

- Ralf

Not applicable

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

Hallo,

thank you very much, it works perfectly!

The power of the knowledge sharing.

Thanks again

Community Browser