Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

peters11
New Contributor

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

Tags (1)
6 Replies
marmentrout5
Contributor

Re: Filter within one table

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
Valued Contributor

Re: Filter within one table

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

marmentrout5
Contributor

Re: Filter within one table

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

peters11
New Contributor

Re: Filter within one table

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
New Contributor II

Re: Filter within one table

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

dwforest
Valued Contributor

Re: Filter within one table

Plugging your fields into Stefan's formula:


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

Community Browser