Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.