Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted

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;

Highlighted

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

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