Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

nate_ak
New Contributor III

Use a single filter to check multiple columns with OR not AND

I would like to allow a user to choose values from a single filter dimension and apply the selection to two columns with OR logic as opposed to AND.  For example:

Account NumberPrimary OfficerSecondary Officer
123AB
456CA
789BC
012AA

 

An officer can be the primary or secondary officer (or both) of an account. I would like to have a single "Officer" filter dimension that a user can choose from to display all accounts an officer is associated with, whether it be primary or secondary (or both). So if a user chose "Officer A" from the filter it should result in the following:

Account NumberPrimary OfficerSecondary Officer
123AB
456CA
012AA

 

All of the data resides in the same source table (QVD). So I don't mind doing the necessary logic in the data load editor or via Master Items. 

Labels (1)
1 Solution

Accepted Solutions
Marcos_rv
Contributor II

Re: Use a single filter to check multiple columns with OR not AND


You could create an auxiliary table that helps you to group, for example:

table_aux:
load accnro,
official1 AS official_aux
resident table 1;

concatenate (table_aux)
table_aux:
load accnro,
official2 AS official_aux
resident table 1;

final:
load distinct
accnro, // key
official_aux as official_final
resident table_aux;

drop table table_aux;

The "final" table will be related by accnro to your fact table, you should use the "official_final" field as a filter, that will work for your OR.

regards!!!!

2 Replies
Marcos_rv
Contributor II

Re: Use a single filter to check multiple columns with OR not AND


You could create an auxiliary table that helps you to group, for example:

table_aux:
load accnro,
official1 AS official_aux
resident table 1;

concatenate (table_aux)
table_aux:
load accnro,
official2 AS official_aux
resident table 1;

final:
load distinct
accnro, // key
official_aux as official_final
resident table_aux;

drop table table_aux;

The "final" table will be related by accnro to your fact table, you should use the "official_final" field as a filter, that will work for your OR.

regards!!!!

nate_ak
New Contributor III

Re: Use a single filter to check multiple columns with OR not AND

Perfect! This did the trick quite nicely. Thank you!