Set Analysis Query

[#1]

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

[its_anandrjs]

Hi,

Write expression

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

Regards

Anand

[Gysbert_Wassenaar]

See attached qvw.

[#4 · Accepted solution]

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 !

[Gysbert_Wassenaar]

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).

[#6]

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 !

[sushil353]

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