Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
SET_Padawan
Contributor III
Contributor III

Filter two fields at the same time

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. 

Labels (1)
1 Reply
mageste
Partner - Contributor III
Partner - Contributor III

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.