Multiple fields linked to a lookup table

[SI_NY02]

I have multiple store fields such as application storeid, purchase storeid, cancel storeid that I need to map/lookup to a store detail lookup table. The store lookup table includes storeid, store name, store address, store region (so more than 2 for mapping).

Can you advise the best way to do this? 

[cristianj23a · Accepted solution]

Hi.

In Qlik Sense, you can achieve this by using the data modeling features, specifically by creating a data association between your main data table (with application storeid, purchase storeid, cancel storeid) and the store detail lookup table. Here's a step-by-step guide on how to do this:

Step 1: Load your data into Qlik Sense First, load your data into Qlik Sense using the data load editor. This involves loading both the main data table (with application storeid, purchase storeid, cancel storeid) and the store detail lookup table. Make sure both tables have a common field, which in this case would be "storeid."

Example script to load the main data table:

MainDataTable:
LOAD
application_storeid,
purchase_storeid,
cancel_storeid
FROM [your_main_data_file.xlsx]
(ooxml, embedded labels, table is Sheet1);

Example script to load the store detail lookup table:

StoreLookupTable:
LOAD
storeid,
store_name,
store_address,
store_region
FROM [your_store_lookup_file.xlsx]
(ooxml, embedded labels, table is Sheet1);

Step 2: Create a data association (link) between the tables To link the two tables based on the "storeid" field, use the "Join" or "Concatenate" function in the data load script. If the field names are different in the two tables, you can use the "as" keyword to rename them to match.

Example script to create a data association:

// Join the main data table with the store lookup table for each store field
// The 'storeid' field in the main data table will be associated with the 'storeid' field in the store lookup table.
Left Join (MainDataTable)
LOAD
storeid,
store_name,
store_address,
store_region
Resident StoreLookupTable;

// Optionally, you can rename the fields in the main data table to indicate their purpose
MainDataTable:
LOAD
application_storeid as storeid_application,
purchase_storeid as storeid_purchase,
cancel_storeid as storeid_cancel
Resident MainDataTable;

Step 3: Use the linked data in visualizations Once the data is associated, you can now use the store details (store_name, store_address, store_region) in your visualizations by referring to the fields in the MainDataTable.

For example, you can create a table or chart with the dimensions as the store_name or store_region, and then use expressions to calculate metrics based on the linked data in the MainDataTable.

You now have a data model in Qlik Sense that links your main data table with the store detail lookup table, allowing you to visualize and analyze the data based on store details.

Regarts.