topic Re: Set expression to find common records in App Development

Set expression to find common records

shirenmathai — Mon, 26 Mar 2018 12:24:24 GMT

Hi All,

Could you please someone help me to correct the set expression. I have two tables, on first table showing the groups and count of users. Second table showing the user details. Users can have multiple groups. If i select multiple groups on first table then users coming in common needs to show on user details table. For example in the attached application if i select group A,B,C then second table should show only the user 1 details, basically filter all sheet objects based on the selected groups in first table with intersect records

Set expression used:-

if(getselectedcount(Group)>0,

count( distinct {<User={"=aggr(count(distinct Group),User)=getselectedcount(Group)"}>} User) ,

count(distinct User)

)

Users:

LOAD * inline [

User, Name

1, ABC

2, BCD

3, EFG

4, RTG

5, YUR

6, IOB

7, LIK

];

Matrix:

LOAD * inline [

Group,User

A,1

B,1

C,1

B,2

A,2

C,3

D,4

C,5

D,5

E,5

F,6

G,7

];

Test.qvf

Re: Set expression to find common records

dwforest — Tue, 27 Mar 2018 23:15:14 GMT

Set Express is not required.

If tables are left as distinct (not joined ) in load, Qlik will handle the filtering for you (and avoid any fan traps).

the formula should simply be count(DISTINCT User)

Re: Set expression to find common records

swuehl — Tue, 27 Mar 2018 23:22:55 GMT

Try

...

count( distinct {<User={"=count(distinct Group)=$(=GetSelectedCount(Group))"}>} User)

...

count( distinct {<User={"=Count(Distinct Group) = Count(Distinct TOTAL Group)"}>} User)

Re: Set expression to find common records

dwforest — Tue, 27 Mar 2018 23:37:10 GMT

Ah, missed the needs to be a member of all groups selected part.

Both of Stefan's formula work with your sample data.

Re: Set expression to find common records

shirenmathai — Wed, 28 Mar 2018 06:17:54 GMT

Thank you swuehl and dwforest ! for the response. In both the cases, the second user table will show records not part of the current selection. For example if we select Group A,B,C then we have only one user in common that is User 1 but table will show other users as well. Is there any way to avoid these records from User table without adding any extra calculation column.

Please find the screenshot.

Re: Set expression to find common records

swuehl — Wed, 28 Mar 2018 14:14:38 GMT

Where have you used the set expression? What's your expression?

I would use User as dimension and

=Maxstring( {<User={"=Count(Distinct Group) = Count(Distinct TOTAL Group)"}>} Name)

as expression

Re: Set expression to find common records

dwforest — Wed, 28 Mar 2018 16:32:43 GMT

As Stefan implies, you must have a measure in the table with the set expression to "filter" the records. If you don't want an "extra" column, you can do a Stefan indicates to display the name as your aggregate. Caveat to that is that measures are not filterable, if your selection yields multiple users that you would like to select in the second table as filters, you'd have to have another column; i usually stick them on the right end of the table as a "flag" field.

Re: Set expression to find common records

shirenmathai — Thu, 29 Mar 2018 05:23:48 GMT

Thank you

Re: Set expression to find common records

shirenmathai — Thu, 29 Mar 2018 05:24:02 GMT

Thank you