Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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 )
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?
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 )
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 !
I added the group by into the expression and it works !!