Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have the following problem:
I have a simple table with person's ID and hobby, but one person can have many hobbies, so may have many rows. I want to filter on hobby and show bar chart of all hobbies of people, who have filtered hobbies, for instance:
"Show me hobbies of people who like sports".
But when I create the filter or shows me only sports (as expected). How do I achieve it? It would be a simple sub-query in SQL.
I know I can load the table twice and do the join in ID, but it's not efficient due to table size.
Regards,
Dominik
In your measure you could use set analysis, COUNT(DISTINCT Hobby). That would remove duplicates when more than one person enjoys the same hobby. Hope this helps!
FYI, there is no set analysis expression in COUNT(DISTINCT Hobby).
It sounds like you want to see the other hobbies of people for the selected Hobby.
This was just answered a couple days ago...
see this post
Set expression to find common records
and the answer by @Stefan Wuhl
You are correct sir. I was thinking SET ANALYSIS, but typed an aggregate. Doh!
Thank you for the reply. You are almost right about the problem - I want to see almost ALL hobbies of people for the selected Hobby. However I do not see how Stefan Wuhl's answer can be applied here, I'm just a qlik beginner....
I have only one table like this:
ID, hobby
1,sports
1,painting
1,singing
2,sports
3,singing
3,painting
lets say I want to see all hobbies of people who's hobby is sports- than should filter people of ID 1 and 2.
How would the expression look like?
count(*) from table name where hobby='Sports'
Plugging your fields into Stefan's formula:
count( distinct {<ID={"=count(distinct hobby)=$(=GetSelectedCount(hobby))"}>} ID)