6 Replies Latest reply: Apr 9, 2015 11:02 AM by Hans Peter Debets

# Create Sum until ...

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

• ###### Re: Create Sum until ...

Hi,

Try this.

T1:

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

FROM

[C:\Users\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

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

FOR EACH vOrder in \$(vOrderList)

T2:

POS

,COST

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

FROM

[C:\Users\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

where ORDER = \$(vOrder)

;

T3:

Load *,1 as temp Resident T2;

Drop table T2;

Next

;

Drop Field temp;

• ###### Re: Create Sum until ...

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

• ###### Re: Create Sum until ...

Works perfect! Thank you!

• ###### Re: Create Sum until ...

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:

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

Resident Table;

DROP Table Table;

• ###### Re: Create Sum until ...

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