topic Re: Set Analysis Query in QlikView

Set Analysis Query

Mon, 22 Dec 2014 14:33:39 GMT

Hi,

I have a scenario wherein There are columns ID, Flag and B_Calc.

I have to calculate sum of a B_Calc for the Least Flag value for each ID in a Pivot table.

How can I achieve in Set Analysis ?

Eg.

Flag	ID
-1	1
0	1
-1	3
0	4

B_Calc	Flag	ID
1	-1	1
2	0	1
300	-1	1
543	-1	3
127	0	4

The result should look something like :

Sum(B_Calc)	Flag	ID
301	-1	1
543	-1	3
127	0	4

Re: Set Analysis Query

its_anandrjs — Mon, 22 Dec 2014 14:55:27 GMT

Hi,

Write expression

Sum({<Flag={'<=$(=0)'}>} B_Calc)

Regards

Anand

Re: Set Analysis Query

Gysbert_Wassenaar — Mon, 22 Dec 2014 15:04:07 GMT

See attached qvw.

Re: Set Analysis Query

Tue, 23 Dec 2014 16:05:26 GMT

Thanks Gysbert !

However I tried using Set Analysis in the Pivot Expression to get the desired result;

sum({<Flag={'-1','0','*'} ,Flag={'-1'},Flag={'0'}>}B_Calc)

Cheers !

Re: Set Analysis Query

Gysbert_Wassenaar — Tue, 23 Dec 2014 20:11:15 GMT

Check the sum for ID 1. It'll be 303 instead of 301.

sum({<Flag={'-1','0','*'} ,Flag={'-1'},Flag={'0'}>}B_Calc) is the same as sum({<Flag={'*'}>}B_Calc) and in this case it's also the same as sum(B_Calc).

Re: Set Analysis Query

Wed, 24 Dec 2014 11:18:40 GMT

Hi,

Thanks for pointing it out !

I Tried using '*' to ensure if a Flag is not assosiated but has B_Cal Value against it as the Columns in my actual App reside in different Tables.

The Change I did is :

sum

({<Flag={'-1','0'} ,Flag={'-1'}>}B_Calc)

Thanks !

Re: Set Analysis Query

sushil353 — Wed, 24 Dec 2014 11:33:10 GMT

Hi,

there is another way..

in your script you can calculate an extra file say countflag:

LOAD AutoNumber(flag,id) as countflag,* Inline
[
flag,id
-1, 1
0 ,1
-1 ,3
0 ,4
];

and then in your expression simply use: Sum({<countflag={1}>}B_Cal)

HTH

Sushil