Working more with larger data sets and increasing number of tables, I’m starting to become more conscious of reducing the amount data which is loaded into one Qlikview file. In my day to day life of writing QV script, I am using the MAPPING LOAD together with the APPLYMAP function to insert fields from one table to a second table without the need of storing two tables.
The best way to explain is by giving an example.
Firstly we have the Orders table which contains details about the placed orders. One field in the Orders table is the EmployeeID indicating which Employee entered the order.
The details about the Employees such as Name, Title, Location etc can be found in a separate table called Employees. We wish to include the Name field from the Employees table, however we do not wish to load the employee data into the Qlikview application. This could be due to the size of the employee’s table or security reasons not to include the details of the employees in the application.
We could load just the EmployeeID and Name to fields from the Employees table and link to the Orders table using the EmployeeID as the key field, however in this example we choose to use the MAPPING LOAD and APPLYMAP functionality.
The script would look as follows:
Step 1: First the Mapping Load to load the Employees.
Employees:
MAPPING LOAD
EmployeeID,
Name;
SQL SELECT * FROM Employees;
Important Note: Mapping load tables can only contain two fields. The first field is the key field used to search between two tables using the APPLYMAP function. The second is the field you wish to transfer to the second table.
Step 2: Load the Orders Table, and use the APPLYMAP function to include the Employee Name.
The syntax for the APPLYMAP function is:
applymap( ‘mapname’, expr [ , defaultexpr ] )
- The first parameters to state which mapping table you wish to use. In our example Employees. Single quotes must be placed around the mapping table name.
- The second parameters states which field in the Orders table is used to link with the MAPPING LOAD table.
- An optional third parameter can be included to express the value that will be returned if there is no match in the mapping load table. If no default value is given, the value will be returned as is.
So the script for the Orders table looks like:
QUALIFY *;
UNQUALIFY CustomerID, OrderID, EmployeeID;
Orders:
LOAD
CustomerID,
EmployeeID,
APPLYMAP ('Employees', EmployeeID, 'Unknown') as EmployeeName,
Freight,
OrderDate,
OrderID,
RequiredDate,
ShipAddress,
ShipCity,
ShipCountry,
ShipName,
ShippedDate,
ShipPostalCode,
ShipRegion,
ShipVia;
SQL SELECT * FROM Orders;
UNQUALIFY *;
In our example, if the EmployeeID is known in the mapping table, then the EmployeeName field would contain the value of the field Name from the Employees table.
In the (unlikely) case that there was an Employee who placed the Order, but not known in the Employees table, then the EmployeeName field would contain the text ‘Unknown’.
Once the script has completely loaded, the mapping table Employees is automatically deleted and not stored in the Qlikview file.
