FREE Online Courses: Knowledge Awaits – Click for Free Access!
In this lesson, we will learn about QlikView mapping and how it is used QlikView scripting. Along with this, we will learn QlikView ApplyMap function and mapping load with syntax and example. So, let’s start with learning the mapping process in QlikView to create mapping tables.
What is QlikView Mapping?
In simplest of words, QlikView Mapping is temporarily making a table (mapping table) using data or field values from pre-existing tables from different models and sources.
The mapping tables are stored in QlikView’s memory only till the time the script is executed and after that, it is automatically removed.
Mapping creates a table with assorted data fields and values whose content can be accessed in different ways through statements or functions (MapSubstring(), ApplyMap(), Rename Field, Map…Using etc.). Using mapping, you can replace field names or values during script execution.
QlikView Mapping Load Statement
The Mapping Load statement is used to load fields and values into a freshly created mapping table in QlikView.
Syntax
Mapping(loadstatement | selectstatement)
The word Mapping is used as a prefix to LOAD or SELECT statements commanding the system to save the loaded fields in the mapping table.
Typically, a mapping table has two columns, first containing values for comparison (as a reference point) and second containing the resultant or desired values based on the comparison. For instance, you can have country codes as the reference column and country names as the second resultant column.
Upon script execution, the country codes will be replaced by the corresponding country names.
Example of mapping load in QlikView.
// Load mapping table of country codes: MapCountry: mapping LOAD * Inline [ CountryCode, Country Sw, Sweden Ind, India Chn, China Ity, Italy, Cnd, Canada Dk, Denmark No, Norway ];
Thus, this will load a mapping table named MapCountry having two columns, namely, CountryCode and Country. The contents of these mapping tables can be used further by mapping statements and functions like ApplyMap.
QlikView ApplyMap Function
The QlikView ApplyMap() function fetches content from an existing mapping table. It maps the result of an expression to an existing field from the mapping table.
Syntax
ApplyMap('map_name', expression [ , default_mapping ] )
Where map_name is the name of the pre-existing mapping table.
The expression is the expression or field whose result should be mapped to a mapping table field.
default_mapping is the value, if mentioned will be returned if there is no match of the field values from the existing mapping table. If not mentioned, the value is returned as it is in the resultant table.
Example of Qlikview ApplyMap Function
Let us get a better understanding of this by the help of an example. Here, we will continue the same country code example as we used in the mapping load section.
// Load list of store managers, mapping country code to country
StoreManagers:
LOAD *,
ApplyMap('MapCountry', CountryCode,'Others') As Country
Inline [
CountryCode, StoreManager
Sw, John Dalton
Cnd, Mary Robins
Ity, Andrea Russo
Arg, Harry Gibson
Dk, William Gilbert
Ind, Indrani Sen
No, Daniel Larsen
Rom, Emilia Mark];
// We don't need the CountryCode anymore
Drop Field 'CountryCode';
Now, the resultant table will look as given below.
| Store Manager | Country |
| John Dalton | Sweden |
| Mary Robins | Canada |
| Andrea Russo | Italy |
| Harry Gibson | Others |
| William Gilbert | Denmark |
| Indrani Sen | India |
| Daniel Larsen | Norway |
| Emilia Mark | Others |
As you can see in the table, the name of the country corresponding to the country code written beside the store manager name is mapped into the final table using an applymap function.
Also, those countries (like Arg and Rom) for which there were no reference country names in the table “MapCountry” are written as “Others”.
Features of QlikView Mapping
Some important features of QlikView mapping are:
- It is similar to the lookup function where a field value is used as a reference to another field existing in the mapping table and returns a matching result.
- In QlikView mapping, always a mapping table is created by Mapping Load or Mapping Select before applying a Map function.
- A mapping table resides temporarily in the memory of QlikView until the time script execution is completed. It is dropped out once a script is executed.
- The temporary mapping table does not affect the main data tables stored in-memory of QlikView.
- Any mapping table can be recalled and reused in a script as many times as a user require.
- In a mapping table, the first field is called as a key and the second field or column is referred to as mapped value ALWAYS.
- All the mapping requirements can do in a single script.
Summary
Thus, this was all about QlikView mapping with QlikView ApplyMap() and mapping load. We learned about what is mapping, how to create mapping tables using the Mapping Load or Mapping Select statements and finally how to use mapping through the ApplyMap() function.
We hope our explanation was helpful. Feel free to share your thoughts with us!