Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this:
GroupId,Name,Score
1,A,2
1,B,3
2,A,3
2,C,4
3,B,2
3,C,3
I want to filter records with groups that contains both A AND B, i.e. only 'GroupId 1' should be selected.
How can I do it in chart view?
May be try this?
Dimension : GroupId
Expression : If(WildMatch(Concat(Name, ','), '*A,B*'), Sum(Score))
Hi, you could just click on 1 in GroupId field and Qlik will filter all rows with 1=GroupId.
Thanks!
Thank you for your reply.
But here is a demo, in my real data, I may want view the data form B AND C, or A AND C dynamically.
What I need exactly is how to map such as 'B and C both in one group' to 'GroupId'.
I am sorry, If i understand wrongly. But as i understand you may need like
If(Match(Name, 'A', 'B'), 'Group A & B', If(Match(Name, 'B, 'C'), 'Group B & C', If(Match(Name, 'A', 'C'), 'Group A & C')))
Hi Anil_Babu_Samineni , thank you for your reply.
But if I understand right, match(Name, 'A', 'B') will produce all rows that 'Name' field contains 'A' or 'B'. What I want is only rows that 'A' or 'B' with same 'GroupId'.
In this demo, I just want Row 1 and 2.
Another question, in your solution, the 'Group A & B' or 'Group B & C' are statically set. But if there are many Name, I may need 'A & B', 'A&C', 'A & B & C' etc, so combining them dynamically is ideal.
What you need output from given input?
GroupId,Name,Score
1,A,2
1,B,3
2,A,3
2,C,4
3,B,2
3,C,3
4, A, 1
4, C, 2
4, B, 3
];
Note : If you need 1 and 4 as output does it mean wherever A & B together from each group you want to present them irrespective of remain?
Yes, in your demo, I want group 1 and 4 as output
May be try this?
Dimension : GroupId
Expression : If(WildMatch(Concat(Name, ','), '*A,B*'), Sum(Score))
Thank for your help.
I changed ' If(WildMatch(Concat(Name, ','), '*A,B*'), Sum(Score))' to ' If(WildMatch(Concat(Name, ','), '*A*B*'), Sum(Score))'
Thus 'A,C,B' can be filtered out also.
Now it works, thank you again.
By the way, can I change 'A AND B' dynamically? For example, I want to view 'A AND B AND C' group, but not change Expression?
Off course, You can even try
If(WildMatch(Concat(Name, ','), '*A*B*') or WildMatch(Concat(Name, ','), '*A*B*C'), Sum(Score))