Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

fisland92
New Contributor III

accumulate with load

hi community,

ı have a data as;

stock value , customer ,      term

10                    a              201401

20                    a               201401

30                    b               201402

40                    c               201403

50                    c               201404

how can we make accumulative sum by term, with load statement?

ı want ıt to be seen as:

term           stock value    

201401            30

201402            60

201403            100

201404            150

thanks

Tags (1)
1 Solution

Accepted Solutions

Re: accumulate with load

No you don't. Since I don't have access to your data source, I used a inline load to get that into the application. You can use this part:

Join(Table) //Replace Table with your fact table you are bringing from SQL

LOAD Term,

  Sum([Stock Value]) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident Table //Replace Table with your fact table you are bringing from SQL

Group By Term;

HTH

Best,

Sunny

View solution in original post

15 Replies

Re: accumulate with load

Try the following script:

Table:

LOAD * INLINE [

    Stock Value, Customer, Term

    10, A, 201401

    20, A, 201401

    30, B, 201402

    40, C, 201403

    50, C, 201404

];

Join(Table)

LOAD Term,

  Sum([Stock Value]) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident Table

Group By Term;


Output looks like this:


Output.PNG


HTH

Best,

Sunny

fisland92
New Contributor III

Re: accumulate with load

but ı already get the data from SQL,

Table:

LOAD * INLINE [

    Stock Value, Customer, Term

    10, A, 201401

    20, A, 201401

    30, B, 201402

    40, C, 201403

    50, C, 201404

];


do ı need to take it again like that? ı already have the data from sql query.

anbu1984
Honored Contributor III

Re: accumulate with load

T1:
Load * Inline [
stock value, customer ,      term
10,a,201401
20,a,201401
30,b,201402
40,c,201403
50,c,201404 ]
;

T2:
Load term,[stock value]+Alt(Peek(CumSum),0) As CumSum;
Load term,sum([stock value]) As [stock value] Resident T1 Group By term Order By term;

Re: accumulate with load

No you don't. Since I don't have access to your data source, I used a inline load to get that into the application. You can use this part:

Join(Table) //Replace Table with your fact table you are bringing from SQL

LOAD Term,

  Sum([Stock Value]) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident Table //Replace Table with your fact table you are bringing from SQL

Group By Term;

HTH

Best,

Sunny

View solution in original post

fisland92
New Contributor III

Re: accumulate with load

ıt says table not found:

new_table:

Join(stok)

LOAD STOK_DONEM,

  Sum(Stok_Bedeli) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident stok

Group By STOK_DONEM

Re: accumulate with load

Table not found? Which table? stok??

Can you share your script for us to look at it?

Best,

Sunny

Highlighted
fisland92
New Contributor III

Re: accumulate with load

yes my main table taking data from oracle is STOK

stok:

select

Stok_Bedeli, // stock value

STOK_DONEM, // term

from ......... ;

ADD

new:

Join(stok) //Replace Table with your fact table you are bringing from SQL

LOAD STOK_DONEM,

  Sum(Stok_Bedeli) + RangeSum(Alt(Peek('Accumulated Stock Value'), 0)) as [Accumulated Stock Value]

Resident stok //Replace Table with your fact table you are bringing from SQL

Group By STOK_DONEM;

anbu1984
Honored Contributor III

Re: accumulate with load

Use Order by clause if term field is not ordered in your sql

Re: accumulate with load

Do you happen to have another table in your script which have the exact same fields as stok? The reason I ask you this is because it seems that QV is not creating the table stok and the only reason I think it might be creating it could be because there is another table with the same field (and field names) and its concatenating this stok table to that table.

And I think anbu cheliyan brings a good point, although unrelated to current problem, that you are probably better of using a order by statement also, just to make sure the accumulation happens correctly.

Sunny