Whenever I interact with a Qlikview user, that you have migrated from Excel recently, one of the most common queries received is:
“How do I apply VLOOKUP () in Qlikview?”
to get started, VLOOKUP is the excellent way to join 2 data sets via a common key. It is something similar to joins in SQL.
Why do we need an external function like VLOOKUP () in Qlikview?
Whenever you import an external dataset into Qlikview, finds common columns and joins itself. Therefore, people somewhat familiar with Qlikview might wonder, Why do we need a function similar to VLOOKUP () first?
The answer is to build efficient data models. Let me explain a real life situation that I had faced in my early days with Qlikview. Some experienced people have told me that it is always wise to minimize the number of tables in your data model. On my first assignment as a Qlikview developer, I used multiple combinations and associations. As a result, my data model became too complex and difficult to understand. I could not easily identify and correct errors (a similar data model example is shown in the figure below for reference).
As I spent more time with Qlikview, I found the use of mapping table with Applymap function () and found they were similar to VLOOKUP (), where we retrieve information from a mapping dataset to a primary dataset. This helps us simplify our data models significantly..
In Qlikview, joins can be made using Association, Join y Applymap. Applymap works similar to VLOOKUP with some additional features. For instance, if VLOOKUP does not find a match in the mapping table, bring back “#NA”. Applymap, Secondly, can return strings / numbers / fields from a similar table, in case there are no matches. We can also write nested Applymap, to reference another mapping table, in case the value is not found.
How can we define the mapping table?
The mapping table must have two columns: the first for the search key and the second for the exchange value. Mapping tables are stored separately in memory and used only during script execution. After running the script, are automatically deleted.
The syntax for loading a mapping table is similar to the LOAD statement with an additional prefix (MAPPING) before LOAD. The source of the mapping table can be a database, a spreadsheet, a resident table, an online table or a QVD file:
Designation_desc: Mapping LOAD Designation_Code, Designation_Desc FROM Data_set.xlsx (ooxml, embedded labels, table is Designation);
Applymap function and syntax ():
The syntax is very similar to a search function: takes a value as input, checks if this value exists in a mapping table and returns the corresponding value from the second column of the mapping table. Applymap function is always used after defining a mapping table:
Syntax:
apply map‘mapname’, search key [ , defaultexpr ] )
where:
mapname is the name of the mapping table, that has already been created through mapping load. Your name must be enclosed in single quotes.
search key is the expression, whose result should be mapped.
defaultexpr is an optional expression, to be used as the default mapping value, if the mapping table does not contain a matching value for the lookup key. If no default value is provided, the search value will be returned as is.
How can we use Applymap?
Let's see a stage, where we have a dataset of employees with their employee code, designation code, salary and marital status. What's more, you need the description of the employee's designation and bonus details, which are available in a different table. You want to calculate the total pay for an employee and report it along with their designation (Total payment = Salary + Bonus). And SQL, you must join the necessary tables to obtain additional information. In excel, we can use Vlookup function () to do this. Here, we will use the Applymap function to map the BONUS and the DESIGNATION DESCRIPTION of the respective mapping table.
Note that this issue can also be resolved via Join or Association. We will solve this using Applymap in this article.
Paso 1:
In our QlikView script, first we will define the mapping tables:
- Bonus – assigns EmpCode to Bonus
- Designation_desc – assigns DesignationCode to the description of the designation
Bonus:
Mapping LOAD EmpCode,
Bonus
FROM Data_set.xlsx (ooxml, embedded labels, table is Bonus);
Designation_desc:
Mapping LOAD DesignationCode,
Designation_Desc
FROM Data_set.xlsx (ooxml, embedded labels, table is Designation);
Paso 2:
The second step is to use this information when reading the employee table:
Employee:
LOAD EmpCode,
Gender,
MaritalStatus,
DesignationCode,
Salary,
ApplyMap('Bonus',EmpCode,0) as Bonus,
ApplyMap('Designation_desc',DesignationCode,'Unknown') as designation_Desc
FROM Data_set.xlsx (ooxml, embedded labels, table is Main);
Now, we have joined the Bonus Description and Designation fields with the employee dataset, without using a combination or association.
Benefits of using ApplyMap:
- Multiple tables in your data model is not a good thing. Therefore, you do not want a situation where you have many searches attached to your master table. This can be solved by creating a mapping table using ApplyMap.
- The mapping table only exists during loading, we can call it as a temporary table.
- ApplyMap has great features as a third parameter (optional). The third parameter allows you to return something more useful, when the lookup value is not in the mapping table. The third parameter can be string, number, base dataset field, mix of string and field and a more important reference to another mapping table using nested Applymap function..
- ApplyMap (‘Bonus’, EmpCode, 0): Cartography Table Map Bonus Amount (bonus). If Empcode does not exist in the mapping table, bring back 0 (zero).
- ApplyMap (‘Designation_desc’, DesignationCode, 'Unknown'): Description of the map designation of the mapping table (Designation_desc). If DesignatonCode does not exist in the mapping table, bring back “Unknown”.
- ApplyMap (‘Bonus’, EmpCode, Salary * 0.05): Cartography Table Map Bonus Amount (bonus). If Empcode does not exist in the mapping table, return the 5% of salary (the field exists in the employee dataset).
- ApplyMap (‘Designation_desc’, DesignationCode, ApplyMap (‘Bonus’, EmpCode, Salary * 0.05)): Description of the map designation of the mapping table (Designation_desc). If DesignatonCode does not exist in the mapping table, devuelve Bonus from Mapping Table (Bonus). Here we have used nested Applymap.
- When the mapping table has duplicate rows, JOIN will result in a double count, while ApplyMap deals with the first instance of the key in the mapping table. Automatically ignore subsequent duplicate values. Again, this is similar to how VLOOKUP works () in excel.
concluding thoughts:
If we require multiple reference table fields based on a key field, we can't do it via Applymap. We can only have two columns in a mapping table. If we are forced to map 4 O 5 fields, then I would prefer to create 4 O 5 mapping tables. But nevertheless, if we need to unite more than that, I will look for other options like Join.
This is an example where we can improve our data models and improve the performance of quality control panels. Do you have any experiences or case studies that illustrate the same in Qlikview? Do you have any other tips to share for improving dashboards??
Share your thoughts through the comments below..
If you like what you have just read and want to continue learning about analytics, subscribe to our emails, Follow us on twitter or like ours page the Facebook.
