Using Qlik ApplyMap()

Qlik Mapping Tables and Functions

The prefix “Mapping” placed before the LOAD statement in Qlik creates a Mapping Table. Mapping table data is not available for and cannot be read directly in resident loads. This data can only be read through the ApplyMap() and MapSubstring() functions.

Where is the ApplyMap() function used in Qlik?

One would use the ApplyMap() function in Qlik to reference data in one dataset to add to the results in another dataset.

Syntax:

ApplyMap('map_name', expression [ , default_mapping ] )

Return data type: dual

Example:
Mapping the Sales Representative Name to the SalesRecords based in the RepresentativeID.

MapSalesRep:
Mapping LOAD RepresentativeID,
RespresentativeName
FROM [$(QVDPath)SalesStaff.qvd] (qvd);

SalesTotals: With ApplyMap we retrieve the mapped value from the mapping table:

LOAD CustomerID,
RepresentativeID,
ApplyMap(‘MapSalesRep’,RepresentativeID) as [Sales Rep],
[Sale Total]

FROM [$(QVDPath)Transactions.qvd] (qvd);

The resulting table:

Note the default_mapping in ApplyMap, this is the value to be returned when no match to the map key in the expression is found:

Mapsubstring maps parts of any expression to information from the mapping table. Where is the MapSubstring() function used in Qlik?

The mapping is case sensitive and non repetitive, results are mapped from left to right

Syntax:  

MapSubstring('map_name', expression)

Return data type: string

Example: 

Mapping what Fire Apparatus are available in each State based on a StateCode:

// Load mapping table of states:
map1:
mapping LOAD *
Inline [
StateCode, StateName
CA, California
TX, Texas
NY, New York
] ;

//StateFireApparatus
AvailableStation:
LOAD *,
MapSubString(‘map1’, StateCode) as Description
Inline [
Station, StateCode
Rescue Truck, CA TX NY
Engine, TX NY
HoverCraft, CA TX
Helicopter, CA NY TX
] ;
// We don’t need the AttCode anymore
Drop Field ‘StateCode’;

The resulting table:

Up Next: Learn How to Start Loving the Qlik Synthetic Key