Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead 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

Thank you in advance

PeterSpunkt

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

6 Replies
iahamedabdullah
Partner - Contributor II
Partner - Contributor II

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
Former Employee
Former Employee

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
Master III
Master III

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;


Not applicable
Author

Works perfect! Thank you!

happydays1967
Creator
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