topic Create Sum until ... in QlikView

Create Sum until ...

Tue, 17 Mar 2015 07:25:26 GMT

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

iahamedabdullah — Wed, 18 Mar 2015 12:58:34 GMT

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;

;

Drop Field temp;

Re: Create Sum until ...

hic — Wed, 18 Mar 2015 13:07:55 GMT

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

robert_mika — Wed, 18 Mar 2015 13:08:18 GMT

Look at this example:

How can I calculate a cumulative sum in load script?

Re: Create Sum until ...

sunny_talwar — Wed, 18 Mar 2015 13:20:36 GMT

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;

Re: Create Sum until ...

Wed, 18 Mar 2015 13:24:56 GMT

Works perfect! Thank you!

Re: Create Sum until ...

happydays1967 — Thu, 09 Apr 2015 15:02:06 GMT

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!