How to create a cumulate field loading a table?

[#1]

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

     A,

     B

FROM

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

(ooxml, no labels, table is Foglio1);

Thank you in advance for your time

[Gysbert_Wassenaar · Accepted solution]

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;

[rbecher]

Another simple variant:

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

- Ralf

[#4]

Hallo,

thank you very much, it works perfectly!

The power of the knowledge sharing.

Thanks again