Qlik Sense ApplyMap vs JOIN: The Complete Performance Optimization Guide

This is Article 4 of the Qlik Sense Data Modeling Course.

What Can You Learn in 8 Minutes About Mapping Tables vs JOINs?

When you need to add a single field to a large table, a LEFT JOIN is often slow and inefficient. This guide introduces the ApplyMap function – an extremely fast alternative that can drastically reduce your script runtimes.

1. ApplyMap() for lightning-fast 1:1 lookups (often 10-100x faster than a LEFT JOIN).
2. Multi-level lookups for hierarchical data (e.g. Customer → Region → Country).
3. A well-informed, performance-critical decision: when to use ApplyMap and when a JOIN is still the better choice.

Time investment: 8 min reading + 45 min hands-on
Prerequisite: JOINs vs KEEPs understood
Quick Win: Implement your first ApplyMap command in 3 minutes

How Do I Get Started with My First ApplyMap Quickly?

The problem: You have a fact table with 1 million orders (Orders) and need to add the CustomerName from a dimension table (Customers) for each order.

The solution with ApplyMap in 2 steps:

1. Create a temporary “dictionary” (a mapping table) using MAPPING LOAD.
2. Apply this dictionary using the ApplyMap() function when loading the fact table.

Step 1: Create the “dictionary”
A MAPPING LOAD table is not a regular Qlik table. It is a highly optimized, temporary key-value structure that exists only during the script run and is automatically deleted afterwards. It must have exactly two columns: the key and the value.

CustomerNameMap:
MAPPING LOAD
    CustomerID,
    CustomerName
FROM [lib://DataFiles/customers.csv]
(txt, utf8, embedded labels, delimiter is ';');

Step 2: Apply the “dictionary”
When loading the Orders table, we use ApplyMap() to look up the corresponding name for each CustomerID.

Orders:
LOAD
    OrderID,
    CustomerID,
    OrderDate,
    Amount,
    ApplyMap('CustomerNameMap', CustomerID, 'Unknown Customer') as CustomerName
FROM [lib://DataFiles/orders.csv]
(txt, utf8, embedded labels, delimiter is ';');

What happens here:

• ApplyMap('MapName', Key, Default):
  • 'CustomerNameMap': The name of the dictionary we created earlier.
  • CustomerID: The key from the Orders table that we look up.
  • 'Unknown Customer': A critically important fallback value if a key is not found.
• Performance advantage: ApplyMap uses a direct hash lookup, which is significantly faster than the complex, row-by-row comparisons of a JOIN. With 1 million rows, the difference can be enormous – from ~45 seconds (JOIN) down to ~8 seconds (ApplyMap).

Checkpoint: Can you see the new CustomerName column in your Orders table? Success! Only seeing NULL values or the default value? Jump to the Troubleshooting section.

What Is the Difference Between ApplyMap, JOIN, and IntervalMatch?

Choose the right method based on your use case. Each technique has its place.

What is the difference between ApplyMap() and JOINs in Qlik Sense?

What is the syntax and parameters for ApplyMap vs JOIN?

The complete parameter list and return value behaviour are documented in the ApplyMap() function reference. The second parameter of the ApplyMap() function can be not just a field name, but any expression. This enables dynamic lookups.

Here we create a map whose value is composed of both name and region.

CustomerMap:
MAPPING LOAD
    CustomerID,
    CustomerName & ' (' & Region & ')' as CustomerInfo
FROM customers.csv;

Orders:
LOAD
    OrderID,
    CustomerID,
    ApplyMap('CustomerMap', CustomerID, 'Unknown') as CustomerInfo,
    ApplyMap('CustomerMap', 'CUST_' & CustomerID, 'Not Found') as AltCustomerInfo,
    ApplyMap('CustomerMap', CustomerID) as CustomerInfoOptional
FROM orders.csv;

What Are Multi-Level Lookups and How Do They Differ from JOINs?

For hierarchical dependencies (Customer → Region → Country), you can nest ApplyMap() calls. Processing happens from the inside out: the result of the inner call becomes the key for the outer call.

First, we create the necessary mapping tables for each level of the hierarchy.

CustomerRegionMap:
MAPPING LOAD CustomerID, RegionID FROM customers.csv;

RegionCountryMap:
MAPPING LOAD RegionID, CountryName FROM regions.csv;

CountryContinentMap:
MAPPING LOAD CountryName, ContinentName FROM countries.csv;

Then we apply the maps in nested form to resolve the full hierarchy.

Orders:
LOAD
    OrderID, CustomerID, Amount,
    ApplyMap('CustomerRegionMap', CustomerID, 'UNKNOWN') as RegionID,
    ApplyMap('RegionCountryMap',
             ApplyMap('CustomerRegionMap', CustomerID, 'UNKNOWN'),
             'Unknown Country') as CountryName,
    ApplyMap('CountryContinentMap',
             ApplyMap('RegionCountryMap',
                      ApplyMap('CustomerRegionMap', CustomerID, 'UNKNOWN'),
                      'Unknown Country'),
             'Unknown Continent') as ContinentName
FROM orders.csv;

Note: With more than two or three levels, this approach quickly becomes unreadable and difficult to debug. In those cases, a multi-step RESIDENT load is often the cleaner solution.

How Do I Optimize MAPPING LOAD for Best Performance?

The Qlik official MAPPING LOAD documentation covers all available options and syntax details. Once your mapping tables are optimized, explore broader performance tuning strategies that cover QVD optimization, memory management, and expression speed.

How Do I Build Mapping Tables Efficiently?

Performance depends heavily on the size of your mapping table. Only load what you actually need.

• Bad: Loads all columns and rows even though only two columns are needed.
• Better: Filters data with WHERE before the map is created. This reduces map size and saves memory.
• Optimal: Cleans the keys (Trim, Upper) directly on load. This prevents mismatches and saves processing time during lookups.

BadMap:
MAPPING LOAD CustomerID, CustomerName FROM customers.csv;

GoodMap:
MAPPING LOAD CustomerID, CustomerName FROM customers.csv
WHERE Status = 'Active';

OptimalMap:
MAPPING LOAD
    Trim(Upper(CustomerID)) as CustomerID,
    CustomerName
FROM customers.csv
WHERE Status = 'Active';

How Does Cached Mapping Work for Repeated Use?

If the source data for your map rarely changes, you can cache it as a QVD. The script checks whether the QVD exists and loads it instead of rebuilding the map from the source on every run.

IF FileSize('lib://Cache/CustomerMap.qvd') > 0 THEN
    CustomerMapData:
    LOAD * FROM [lib://Cache/CustomerMap.qvd] (qvd);
ELSE
    CustomerMapData:
    LOAD CustomerID, CustomerName FROM customers.csv WHERE Status = 'Active';

    STORE CustomerMapData INTO [lib://Cache/CustomerMap.qvd] (qvd);
END IF

CustomerMap:
MAPPING LOAD * RESIDENT CustomerMapData;
DROP TABLE CustomerMapData;

How Can You Use ApplyMap for Data Quality?

ApplyMap is an extremely fast method for validating or standardizing data.

Here we create a validation map that contains only valid customers. In a second step, we use a standardization map to unify different spellings for country names.

ValidCustomersMap:
MAPPING LOAD
    CustomerID,
    'VALID' as Status
FROM customers.csv
WHERE Status = 'Active' AND Len(CustomerName) > 0;

CountryStandardMap:
MAPPING LOAD Old, New INLINE [
Old, New
"Deutschland", "Germany"
"UK", "United Kingdom"
"USA", "United States"
];

Orders:
LOAD
    OrderID, CustomerID, Country, Amount,
    ApplyMap('ValidCustomersMap', CustomerID, 'INVALID') as CustomerValidation,
    ApplyMap('CountryStandardMap', Country, Country) as StandardCountry
FROM orders.csv;

How Do You Fix Common Problems with Mapping Tables vs JOINs?

Why Does ApplyMap Always Return the Default Value?

Problem: The function finds no matches. This is almost always caused by keys that do not match exactly.

Troubleshooting checklist:

1. Data type mismatch (number vs. text): The CustomerID 123 is not the same as '123'. Ensure consistency, e.g. with Num(CustomerID).
2. Invisible whitespace: The value 'CUST01 ' is not the same as 'CUST01'. Use Trim() on both sides.
3. Case sensitivity: The value 'cust01' is not the same as 'CUST01'. Use Upper() on both sides.

Best practice: always clean keys proactively directly in the MAPPING LOAD.

Why Is Performance Worse Than Expected?

Problem: Your ApplyMap is not as fast as expected.

Root cause: This usually happens when the mapping table itself is extremely large (e.g. more than 5-10 million rows). ApplyMap is optimized for lookups in “small” to “medium-sized” dimensions. When your “dimension” is enormous, a LEFT JOIN can sometimes be faster because the Qlik engine optimizes it differently.

With this pattern you can dynamically select the strategy based on the size of the lookup table.

LookupSizeCheck:
LOAD Count(*) as LookupSize FROM customers.csv;
LET vLookupSize = Peek('LookupSize', 0, 'LookupSizeCheck');
DROP TABLE LookupSizeCheck;

IF $(vLookupSize) < 5000000 THEN
    CustomerMap:
    MAPPING LOAD CustomerID, CustomerName FROM customers.csv;

    Orders:
    LOAD *, ApplyMap('CustomerMap', CustomerID, 'Unknown') as CustomerName
    FROM orders.csv;
ELSE
    Customers:
    LOAD CustomerID, CustomerName FROM customers.csv;

    Orders:
    LOAD * FROM orders.csv;

    LEFT JOIN (Orders)
    LOAD CustomerID, CustomerName RESIDENT Customers;
    DROP TABLE Customers;
END IF

What Are the Next Steps in the Course?

Up next: Data Transformation – String, Math, Date Functions

Related topics:

• JOINs vs KEEPs: When to Use Which Strategy
• Data Transformation – String, Math, Date Functions
• Transposing Data – CROSSTABLE vs GENERIC LOAD
• Incremental Loading – Loading Only Changes

Previous: JOINs vs KEEPs | Next: Data Transformation