Filter two fields at the same time

SET_Padawan — Wed, 19 Jul 2023 08:12:28 GMT

I have two columns in a table where one row represents one transaction. The first column represents which location a package originated from. The other column represents which location the package is going. How can I implement a filter or toggle that when clicked will filter the whole sheet to transactions where a specific pair of locations are involved?

So basically I want an option to view only transactions where it was either Location A to Location B or Location B to Location A.

Re: Filter two fields at the same time

mageste — Mon, 24 Jul 2023 20:43:25 GMT

Hi there!

This is a scenario in which you can use a LinkTable to solve it.

I am sending a solution that may help you, but I also suggest you to read about the LinkTable concepts, it is a really useful concept to master on data modelling in Qlik Sense:
https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

And now for your problem, try something like this:

Table:
Load * Inline [
Origin, Destination
USA, Brazil
USA, Germany
Brazil, Spain
Brazil, Netherlands
France, Japan
Japan, Spain
Spain, USA
Germany, USA
Germany, Japan
Japan, Brazil
Spain, France
];

Fact: //Create a key field in your table with the fields you want to filter
Load
Origin,
Destination,
Origin & ' - ' & Destination as Key_Location
Resident Table;

Drop Table Table; // drop the former table to avoid synthetic keys

LinkTable: // The linktable will have a concatenate block for each field you want to filter together
Load
Origin as LinkTable_Origin,
Key_Location,
Origin & ' - ' & Key_Location as Key_Dimension
Resident Fact;

Concatenate (LinkTable)
Load
Destination as LinkTable_Destination,
Key_Location,
Destination & ' - ' & Key_Location as Key_Dimension
Resident Fact;

Dimension: // Finally you build the field/dimension you will use in your filters
Load Distinct
Origin as Location,
Origin & ' - ' & Key_Location as Key_Dimension
Resident Fact;
Concatenate(Dimension)
Load Distinct
Destination as Location,
Destination & ' - ' & Key_Location as Key_Dimension
Resident Fact;

Drop Fields LinkTable_Origin, LinkTable_Destination; // a last drop to avoid synthetic keys

Hope that helps, and feel free to ask any questions.

topic Filter two fields at the same time in App Development

Filter two fields at the same time

Re: Filter two fields at the same time