Qlik Sense Mapping Functions With Syntax

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense Scatter Plot Visualizations. Today, we will see Qlik Sense Mapping Functions. Moreover, we will look at mapping functions with syntax. We use the mapping functions for managing and working with mapping tables. Usually, we use the mapping tables in BI tools such as QlikView and Qlik Sense to map or replace field values or field names at the time of script execution. Basically, there are two Qlik Sense Mapping Functions, which we use in data load script, ApplyMap() and MapSubstring() function.

So, let’s start Qlik Sense Mapping Functions Tutorial.

Have a look at Qlik Sense Line Chart

2. Qlik Sense Mapping Functions

Below are some Mapping Functions in Qlik Sense –

i. ApplyMap() function

We use this function to map or mirror the output of an expression, that evaluates during script execution, in a previously loaded table.

The syntax of Qlik Sense ApplyMap() function.

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

Where map_name is the name of the table from which we want to map the fields or field values into a new table. This table will read by mapping LOAD or Mapping SELECT statements prior to applying the ApplyMap function.

The expression is that piece of quote or a condition, evaluation of whose result we need to map.

The default_mapping is a conditional parameter which specifies the value that we use when the table evaluates for the expression condition, does not contain a certain value. If the default mapping value is not set then the value of the expression will return as it is.

Do you know about Qlik Sense Exponential and Logarithmic Functions

For instance,

// Load mapping table of country codes:
map1:
mapping LOAD *
Inline [
CountryCode, Country
Swd, Sweden
Dmk, Denmark
Nor, Norway
] ;
LOAD *,
ApplyMap( 'map1', CountryCode ) As Country
Inline [
CountryCode, Salesperson
Swd, John
Swd, Mary
Swd, Parker
Dmk, Josh
Dmk, Olivia
Nor, Emma
Nor, Fred
] ;
// We don't need the CountryCode anymore
Drop Field 'CountryCode';

The resulting table is given below, where, using the ApplyMap() function the Country field is mapped using the CountryCode field in the expression.

Salesperson	Country
John	Sweden
Mary	Sweden
Parker	Sweden
Josh	Denmark
Olivia	Denmark
Emma	Norway
Fred	Norway

ii. MapSubstring() function

The MapSubstring() function maps a substring or a part of a data field from a previously loaded data table (using mapping LOAD/SELECT statements) to another table. This function is case-sensitive and returns a string type data value.

The syntax of Qlik Sense MapSubstring() function:

MapSubstring('map_name', expression)

Where map_name is the name of previously loaded table using the mapping LOAD/SELECT statement.

Expression is that statement whose result is to be mapped as substrings on the new table.

For example, first load a table named ‘attributes’ having two fields, AttributeCode and the Attribute to which each code corresponds. Then in the next table, we will load a table named ‘Productmodels’ in which we will use the values of attribute table by applying MapSubstring function into the Productmodels table. In the resultant table, all the attribute names have been loaded in the Description table by just mapping the AttributeCode substring in the Productmodels function.

Let’s revise Qlik Sense Color Functions

attributes:
mapping LOAD *
Inline [
AttributeCode, Attribute
R, Red
Y, Yellow
B, Blue
C, Cotton
P, Polyester
S, Small
M, Medium
L, Large
] ;
Productmodels:
LOAD *,
MapSubString('attributes’, AttributeCode) as Description
Inline [
Model, AttributeCode
Twixie, R C S
Boomer, B P L
Raven, Y P M
Seedling, R C L
SeedlingPlus, R C L with hood
Younger, B C with patch
MultiStripe, R Y B C S/M/L
] ;
// We don't need the AttributeCode anymore
Drop Field 'AttributeCode';

Below, is the resultant table. Here, with the help of MapSubstring function, the respective attributes of the given models have been mapped in the field Description using AttributeCode field values.

Model	Description
Twixie	Red Cotton Small
Boomer	Blue Polyester Large
Raven	Yellow Polyester Medium
Seedling	Red Cotton Large
SeedlingPlus	Red Cotton Large with hood
Younger	Blue Cotton with patch
MutliStripe	Red Yellow Blue Cotton Small/Medium/Large

So, this was all in Qlik Sense Mapping Functions. Hope you like our explanation.

3. Conclusion

Hence, these were all the mapping functions used in Qlik Sense to map table fields or substrings from the field. Still, if you have any query regarding Qlik Sense Mapping Functions, ask in the comment tab.

See also –

Qlik Sense Treemap Visualization

Reference for Qlik Sense