topic Re: accumulate with load in QlikView

accumulate with load

Anonymous — Tue, 12 May 2015 13:36:42 GMT

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

Re: accumulate with load

sunny_talwar — Tue, 12 May 2015 13:48:03 GMT

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:

HTH

Best,

Sunny

Re: accumulate with load

Anonymous — Tue, 12 May 2015 13:54:43 GMT

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.

Re: accumulate with load

anbu1984 — Tue, 12 May 2015 13:55:22 GMT

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

sunny_talwar — Tue, 12 May 2015 13:57:08 GMT

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

Re: accumulate with load

Anonymous — Tue, 12 May 2015 14:01:55 GMT

ı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

sunny_talwar — Tue, 12 May 2015 14:03:43 GMT

Table not found? Which table? stok??

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

Best,

Sunny

Re: accumulate with load

Anonymous — Tue, 12 May 2015 14:06:58 GMT

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;

Re: accumulate with load

anbu1984 — Tue, 12 May 2015 14:07:15 GMT

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

Re: accumulate with load

sunny_talwar — Tue, 12 May 2015 14:11:59 GMT

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

Re: accumulate with load

Anonymous — Tue, 12 May 2015 14:29:08 GMT

ı checked but no....

no solution .....

nothing in hand.

Re: accumulate with load

sunny_talwar — Tue, 12 May 2015 14:34:04 GMT

Would you be able to share a sample?

Re: accumulate with load

john_s_w_wood — Tue, 12 May 2015 15:14:51 GMT

Just thought I'd check something before I looked at the script?

Do you need to perform it in the script? You can get the result you want in a chart such as a straight table from your original data?

ensure the sort is on term

term as dimension

expression sum(stock value) but tick accumulate total in the bottom LH corner of expressions.

Re: accumulate with load

Anonymous — Tue, 12 May 2015 15:26:48 GMT

Ok my friends. It worked.

The problem is CASE SENSITIVITY (upper case problem) ı solved it.

Thank you very much for your helps.

new:

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

Order By STOK_DONEM;

that works. thanks.

Re: accumulate with load

Anonymous — Tue, 12 May 2015 15:41:15 GMT

yes ı tried it but it gives dynamic sum, not static,

when you filter the 'term' it changes the sum,

but we had needed static solutions here.

thanks anyway.

Re: accumulate with load

john_s_w_wood — Tue, 12 May 2015 16:06:03 GMT

you've got your answer so thats good

Its academic now but another way is to use the rangesum and above functions. Ive reproduced your requirement using both the accumulate and the rangesum methods

rangesum(above(sum(stockvalue),0,rowno())) (doesnt need the accumulate flag)

Kind Regards