Create Sum until ...

[#1]

Hello, soory for my bad English.

I want to create SUM as shown in the example table.I need to do this in the script not in a table diagramm.

SUM ist the accumulated sum of COST for each POS of ORDER

Thank you in advance

PeterSpunkt

[iahamedabdullah]

Hi,

Try this.

T1:

LOAD CONCAT(distinct ORDER,',') as T_Order

FROM

(ooxml, embedded labels, table is Sheet1)

;

LET vOrderList = PEEK('T_Order',0,'T1');

FOR EACH vOrder in $(vOrderList)

T2:

LOAD ORDER,

     POS

    ,COST

    ,IF(ISNULL(PEEK('COST'))=-1,COST,PEEK(PREORDER)+COST) as PREORDER

FROM

(ooxml, embedded labels, table is Sheet1)

where ORDER = $(vOrder)

;

T3:

Load *,1 as temp Resident T2;

Drop table T2;

Next

;

Drop Field temp;

[hic · Accepted solution]

tmp:

Load ORDER, POS, COST From ... ;

Data:

Load ORDER, POS, COST,

  If(ORDER=Peek(ORDER), RangeSum(COST, Peek(Accumulated)), COST) as Accumulated

Resident tmp

   Order By ORDER, POS;

Drop Table tmp;

/HIC

[robert_mika]

Look at this example:

How can I calculate a cumulative sum in load script?

[sunny_talwar]

Try this:

Table:

LOAD * INLINE [

    ORDER, POS, COST

    100, 10, 1

    100, 30, 2

    100, 50, 2

    100, 70, 3

    200, 10, 2

    200, 20, 3

    200, 50, 2

    200, 100, 5

    300, 20, 3

    300, 40, 2

    300, 60, 2

    300, 110, 1

    400, 10, 1

    400, 20, 0

    400, 30, 2

];

Table1:

LOAD *,

  If(Peek('ORDER') = ORDER, RangeSum(Alt(Peek('SUM'), 0)) + COST, COST) as SUM

Resident Table;

DROP Table Table;

[#6]

Works perfect! Thank you!

[happydays1967]

When loading from other source, make sure to use the proper ORDER BY with the LOAD statement, otherwise you might get really wrong results!

Thanks for the code, saved my day!

HP