Count flags with aggregation

[zet]

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 !

[edwin]

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?

[MayilVahanan · Accepted solution]

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 )

[zet]

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 !

[zet]

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