topic Re: Count flags with aggregation in QlikView

Count flags with aggregation

zet — Sat, 16 Nov 2024 17:49:45 GMT

Hello,

I have a qvd file wich contains a table like this and I want to count the Yes flags group by Places and Type then count No flags group by Places and Type but without counting the Yes counted before.

Places	Type	Flag
Place1	A	No
Place1	A	No
Place2	B	No
Place2	B	No
Place3	C	No
Place3	C	No
Place3	A	Yes
Place3	A	No
Place4	C	Yes
Place4	C	No

II want to insert a table with an expresssion that will generate a table like this :

Flags	Count(Flags)
Yes	2
No	3

The "3 No" should be counted like that = 1 Place1_A + 1 Place2_B + 1 Place3_C + (0 Place3_A + 0 Place4_C) because they were already counted with the Yes flags.

Thanks in advance for your help !

Re: Count flags with aggregation

edwin — Fri, 13 Nov 2020 20:40:58 GMT

can you pls give sample data especially showing the scenario where the condition

(0 Place3_A + 0 Place4_C) because they were already counted with the Yes flags.

exists as its not very clear just reading your post. also what does 1 Place1_A + 1 Place2_B + 1 Place3_C mean?

Re: Count flags with aggregation

MayilVahanan — Mon, 16 Nov 2020 07:46:09 GMT

HI @zet

Try like below

temp:
LOAD * INLINE [
Places,Type, Flag
Place1, A, No
Place1, A, No
Place2, B, No
Place2, B, No
Place3, C, No
Place3, C, No
Place3, A, Yes
Place3, A, No
Place4, C, Yes
Place4, C, No
];

Load Places&'_'&Type as Key,Flag, 1 as NoOfFlag Resident temp Where Flag = 'Yes';
Load Places&'_'&Type as Key,Flag, 1 as NoOfFlag Resident temp Where Flag = 'No' and not Exists(Key, Places&'_'&Type);

DROP Table temp;

Dim: Flag

Measure: Sum(NoOfFlag )

Re: Count flags with aggregation

zet — Mon, 16 Nov 2020 14:07:41 GMT

Thank you for your response @MayilVahanan

But I think we should add a group by somewhere because if the last line was (Place4,C,Yes) instead of (Place4,C,No) we'll have 3 Yes and 3 No wich is false.

I didn't know how to add the group by.

Could you please help !

Re: Count flags with aggregation

zet — Mon, 16 Nov 2020 16:31:20 GMT

I added the group by into the expression and it works !!