topic Re: Issue in accumulation in load script in QlikView

Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 06:22:10 GMT

Hi,

I have issue in accumulation at back end, when I am loading negative values, I am unable to get desired output.

Below are the script, qlikview output and desired output.

Kindly suggest.

Script:

BB:

LOAD * INLINE [

A,B,C,D, Sales

1,q,r,e, 200

1,q,w,e, -300

3,p,o,i,400

4,p,k,j,100

4,p,k,j,-900

5,v,b,n,-200

6,e,d,x, 200

];

CC:

LOAD

Rangesum(Sales,Peek('CumSales')) as CumSales

Resident BB;

DROP Table BB;

Qlikview output:

Desired Output:

A	Sum(Sales)	Sum(CumSales)

1	-100	-100
3	400	300
4	-800	-500
5	-200	-700
6	200	-500

Re: Issue in accumulation in load script

Anil_Babu_Samineni — Tue, 27 Dec 2016 06:28:58 GMT

Check this

Does this you need from Script level or UI

Re: Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 06:33:08 GMT

I need this at script level and with respect to all of my dimensions. If I take one or more than one dimensions then values should be accumulate correctly.

Re: Issue in accumulation in load script

Anil_Babu_Samineni — Tue, 27 Dec 2016 10:12:14 GMT

Why you need script level, Why not Simple Full accumulation for this? Is there any link to other symptoms to show those? Can you confirm the same. Even, If you use script level what was the use?

Re: Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 10:15:32 GMT

I am working in qliksense (where is no option for accumulate values in tables) and need to map as on stock with every date. So I need accumulate values with respect to every date in SAP mseg table.

Re: Issue in accumulation in load script

Anil_Babu_Samineni — Tue, 27 Dec 2016 11:36:43 GMT

Make Sense, But why not in Qlik sense Front-End side

I used Dimension as A

Expressions are

1) Sum(Sales)

2) Rangesum(Below(Sum(Sales),0),Above(Sales,1,10))

Re: Issue in accumulation in load script

sunny_talwar — Tue, 27 Dec 2016 11:50:12 GMT

I agree with Anil that it might make sense to do this on the front end unless you don't want to filter your accumulation based on other attributes. To simplify Anil's solution a little, you can use this:

RangeSum(Above(Sum(Sales),0, RowNo()))

Re: Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 12:02:01 GMT

I have already tried this but I need in script. Because I need stock as of an specific date which can only be calculated from script. If here in front end when you will select any specific date then it will show only the entries of that date, not accumulation.

I have tried accumulation in script, and that also giving me desired values (when all values are positive), but here I also have negative values..

I need solution at urgent basis. Please provide if you got.

Thanks in advance.

Re: Issue in accumulation in load script

sunny_talwar — Tue, 27 Dec 2016 12:14:45 GMT

With regards to the date issues you mentioned, those can be resolved using set analysis. But if you still insist, try like this:

Table:

LOAD * INLINE [

A,B,C,D, Sales

1,q,r,e, 200

1,q,w,e, -300

3,p,o,i,400

4,p,k,j,100

4,p,k,j,-900

5,v,b,n,-200

6,e,d,x, 200

];

LinkTable:

LOAD A,

RangeSum(Peek('CumSales'), SumSales) as CumSales;

LOAD A,

Sum(Sales) as SumSales

Resident Table

Group By A

Order By A;

Best,

Sunny

Re: Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 12:20:53 GMT

Thanks for your valuable time.

But I have also tired this method. I have many dimensions and I need accumulated data with respect to all of the dimensions.

If I select B as dimension in table then it also should be accumulate properly.

Re: Issue in accumulation in load script

sunny_talwar — Tue, 27 Dec 2016 12:24:15 GMT

Two ways

1) Do it on the front end (you can take care of selection in date using set analysis. If you need help, provide a sample where you have dates and we might be able to help)

2) Create multiple accumulated tables for each of your dimension (wouldn't advice you to do this, but in the script, that is the only option available)

HTH

Best,

Sunny

Re: Issue in accumulation in load script

rkspareek1992 — Tue, 27 Dec 2016 12:36:07 GMT

Thanks to both of you for your valuable time.

But I cannot use both of the methods.

Kindly check if there is any other methods.

Re: Issue in accumulation in load script

sunny_talwar — Tue, 27 Dec 2016 12:38:52 GMT

I hope someone else might be able to help you out, but with the limited information you have been providing I won't be of much help.

All the best

Best,

Sunny

Re: Issue in accumulation in load script

sunny_talwar — Tue, 27 Dec 2016 12:40:16 GMT

And just for your future reference

QlikCommunity Tip: How to get answers to your post?