Qlik Sense Mapping Functions With Syntax

FREE Online Courses: Click for Success, Learn for Free - Start Now!

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.

Qlik Sense Mapping Functions With Syntax

Qlik Sense Mapping Functions With Syntax

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.

SalespersonCountry
JohnSweden
MarySweden
ParkerSweden
JoshDenmark
OliviaDenmark
EmmaNorway
FredNorway

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.

ModelDescription
TwixieRed Cotton Small
BoomerBlue Polyester Large
RavenYellow Polyester Medium
SeedlingRed Cotton Large
SeedlingPlusRed Cotton Large with hood
YoungerBlue Cotton with patch
MutliStripeRed 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

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *