Announcements
cancel
Showing results for
Did you mean:
Not applicable

## 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

PeterSpunkt

1 Solution

Accepted Solutions
Former Employee

tmp:

Load ORDER, POS, COST From ... ;

Data:

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

Resident tmp

Order By ORDER, POS;

Drop Table tmp;

/HIC

6 Replies
Partner - Contributor II

Hi,

Try this.

T1:

FROM

(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

(ooxml, embedded labels, table is Sheet1)

where ORDER = \$(vOrder)

;

T3:

Load *,1 as temp Resident T2;

Drop table T2;

Next

;

Drop Field temp;

Former Employee

tmp:

Load ORDER, POS, COST From ... ;

Data:

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

Resident tmp

Order By ORDER, POS;

Drop Table tmp;

/HIC

Master III
MVP

Try this:

Table:

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;

Not applicable
Author

Works perfect! Thank you!

Creator

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

Community Browser