Mapping Script Functions

Mapping functions are used to apply a previously loaded mapping table to a field. Doing so allows for mapping values from the mapping table to the field values.

This article will cover the basic principles of working with these functions in the data load editor, including:

  • The general function syntax
  • Working with mapping functions in the data load editor

For a complete up-to-date list of mapping functions, please see the Qlik Sense help pages here.

The mapping function syntax

As of right now, there are only two mapping functions. We will decipher the syntax for both functions below.

Example 1: ApplyMap

The ApplyMap() function is used to map values from a previously created mapping table to a field or expression result.

Copy code 


ApplyMap('mapname', expr [ , defaultexpr ] )

The rules for reading the function syntax are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there are three elements: ‘mapname’, expr and [defaultexpr].
  3. Anything between square brackets [] is an optional parameter. In this function, the parameter [defaultexpr] is an optional parameter and it allows you to specify the value to return if there is no match between the value and the map. If the parameter is omitted, NULL will be returned. If you do include the parameter, DO NOT include the square brackets.

An example of this function being used could be:

Copy code 


ApplyMap('SALE_STATUS',Status,'Unknown')

Assume that the table SALE_STATUS contains two fields: the sale status enum and a description of what the enum means. The above function will look in the current table at the field “Status”, which contains sale status enums, and will return the corresponding enum description from the mapping table. It is similar to the IndexMatch function in Excel. Where there is no match, the string ‘Unknown’ will be returned. If this parameter was omitted, a NULL would be returned where there are no matches.

Example 2: MapSubstring

The MapSubstring() function is used to map values from a previously created mapping table to a substring within a  field or expression result. Beware that every instance of a matching substring will be mapped. For example, if you are mapping the string ‘Done’ to a ‘D’ then the string ‘Delivery’ will become ‘Doneelivery’.

Copy code 


MapSubstring('mapname', expr)

The rules for reading the function syntax are as above. There are no optional parameters in this function.

An example of this function being used could be:

Copy code 


MapSubString('SALE_STATUS',OrderStatus)

The above function will return the value from the mapping table SALE_STATUS by replacing each matching substring in the field “OrderStatus”.

How to use mapping functions in the data load editor

Here are some important points on using mapping functions in the data load editor:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • They are particularly useful if you need to map values in multiple tables from one source. Using a map will avoid doing many joins to the same dataset
  • The functions can be nested and combined with other types of functions

Here is an example of both mapping functions being used:

Copy code 


CUSTOMER_TYPE:
MAPPING LOAD * INLINE [
  ID, Type 
  1, Domestic - private
  2, Domestic - business
  3, International - private
  4, International - business
];

SALE_STATUS:
MAPPING LOAD * INLINE [
  Status, Descr 
  N, Not sent
  S, Sent
  D, Delivered
  P, Part paid
  F, Fully paid
  U, Payment not cleared
];

SALES:
LOAD
	"ID",
    "Date",
    "Customer ID",
    "Order Number",
    "Value",
    "Customer Type ID",
    "Order Status",
	ApplyMap('CUSTOMER_TYPE',"Customer Type ID", 'Unknown') as "Customer Type",
    MapSubString('SALE_STATUS',"Order Status") as "Order Status Descr"
    
FROM [sales.qvd] (qvd);

The field “Customer Type ID” contains enums to signify the type of customer the order is for and the newly created field “Customer Type” maps the relevant description. The field “Order Status” contains one letter status separated by a ‘;’ e.g., ‘F;N’ would signify the order is fully paid for but not yet sent out. The values in the mapping table SALE_STATUS will be mapped to these values in the field “Order Status Descr”.