Qlik Sense Mapping Functions With Syntax
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.
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.
// 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.
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:
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.
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.
|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.
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 –