Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filter within one table

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

6 Replies
Tanalex
Creator II
Creator II

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!

dwforest
Specialist II
Specialist II

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

Tanalex
Creator II
Creator II

You are correct sir.  I was thinking SET ANALYSIS, but typed an aggregate.  Doh!

Anonymous
Not applicable
Author

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?

rajasaravananm
Contributor II
Contributor II

count(*) from table name where hobby='Sports'

dwforest
Specialist II
Specialist II

Plugging your fields into Stefan's formula:


count( distinct {<ID={"=count(distinct hobby)=$(=GetSelectedCount(hobby))"}>} ID)