This is the second post in a series on merging tables in QlikView. In the previous post I wrote about appending rows to tables by using the CONCATENATE prefix, this time I will show you how to add columns to a previously loaded table by using the JOIN prefix.
Table of contents
The story so far
In the previous post we discussed the example of a paper company that wanted to merge the employee list of their Sales Department of one of their branches (the Stamford branch) into the employee list of another branch office (the Scranton branch). We merged the two tables by using the CONCATENATE prefix, demonstrating the difference between implicit and explicit concatenation along the way. The resulting table is shown on the right.
The new requirements
Now that we have a single, merged Employee table, the manager of the Scranton branch comes up with an additional requirement; wouldn’t it be a cool idea if we could link corporate’s HR data with the merged employee table so we also have everyone’s job description and pay scale available in our reports and dashboards? While I personally reserve the “cool idea” qualification for ideas that are really, well, cool (say, shark cage diving or designing your own heart valve implant) for the sake of this post lets assume that this is a cool idea and that we really want to implement it. So how do we proceed?
Just load the table
The first option we could consider is just loading the table as-is. The resulting data model would look like the picture on the right. Technically, there is nothing wrong with using this approach but I prefer to merge the tables for two reasons:
- It makes for a simpler, less-cluttered data model;
- Logically, it makes more sense for employee-related data to be grouped together into a single employee dimension table.
To merge the columns from two (or more) tables we can use the JOIN prefix.
The JOIN prefix
When the JOIN prefix is put in front of a LOAD statement, it tells QlikView not to load the data into a new table, but instead join the data to a previously loaded table.
The join is a natural join made over all the common fields. This means that QlikView compares all the common fields between the two tables and joins the rows from both tables when the values in the shared columns match.
Optionally, the name of the table to which the data should be joined can be specified between parentheses. If no table name is specified, QlikView assumes that the data should be joined with the last loaded table. Those who read the first post know that I am not a big fan of making assumptions in the load script, it makes the code harder to read and maintain. Therefore I always specify a table name.
Join types
When you look closely at the two tables that we want to join, you notice that not all Person ID’s correspond between the tables. The Employees tables contain an employee, Michael Scott, who is not present in the HR Data table. Conversely, the HR Data table contain data for a Receptionist who is not present in the Employees table.
When we do a standard join, the tables are merged so that the corresponding records from both tables are shown on the same row. Records without a corresponding record in the other table get null (empty) values for those columns. The result is the following table:
This is almost the merged table we want, but it still contains data for a Receptionist who is not an employee at the Scranton branch. Fortunately there is a way to solve this, by specifying the join type.
By using a join type we can specify how QlikView joins the tables, we can use the following options:
- INNER: Only records which are present in both tables are included in the merged table. In our example this would mean that both Person ID 1 (Michael Scott) and 10 (Receptionist) would be excluded from the merged table.
- OUTER: All records from both tables are included in the merged table, regardless if they have corresponding records in the other table. This returns the same result as the standard join.
- LEFT: All records from the first table are included in the merged table, and only those records from the second table which have a corresponding value in the first table. In our example this would mean that Person ID 1 (Michael Scott) would be included and that Person ID 10 (Receptionist) would be excluded, as it has no corresponding value in the first table.
- RIGHT: All records from the second table are included in the merged table, and only those records from the first table which have a corresponding value in the second table. In our example this would mean that Person ID 10 (Receptionist) would be included and that Person ID 1 (Michael Scott) would be excluded, as it has no corresponding value in the second table.
As we want to keep the entire Employee table and only add the corresponding values from the HR Data table to it, we decide to use the LEFT JOIN prefix.
This results in the following merged table:
And that’s how it’s done! The example QVW for this post shows the completed script and also shows what result you get when you use another join type. As always, if you have any questions or comments, feel free to drop them in the comments section.
Downloads
Download the example QVW and source data
12 Comments
Awesome article… thanks a lot for sharing…. What will be your next about and how long we have to wait for it ?
Thanks. I am not yet sure what the next post will be about and when it will be there. I have a whole bunch of ideas and articles in various states of completion, but no fixed planning for posting/writing them.
If you want to be kept up to date, if you sign up on the right-hand side (near the top of the page) you will be notified when a new article is posted.
I have question how would we manage self join. Hope you are aware of employee table of oracle. Can you tell more about it. How to handle in Qlikview
Chetan,
Yes, I am aware of the Employee table in Oracle, Scott’s working on that one right?
With a self-join you have multiple options, but the two that come to mind are:
- If there are very few levels, say only an employee and a manager (2 levels), then you could load the table twice and use a left join, so in pseudocode:
load
empno,
ename,
mgr
from employee;
left join
load
empno as mgr
ename as mgr_name
resident employee;
- If there are more levels, or if you do not know beforehand how many levels there are, you can use the “hierarchy” prefix. The QlikView help file explains this quite well. Basically, you tell QlikView which field contains the ID of the node (in this case Employee) and the field that contains the ID of the parent-node (in this case Manager). In pseudocode:
hierarchy(empno, mgr, ename)
load
empno,
ename,
mgr
from employee;
This will “flatten out” the table. Hope this explains it adequately. I will probably expand upon this in a future blog post.
Kind regards,
Barry
What if you want to put a value on the null field?
Like Department ‘Management’ would have a salary scale of 10 and not null?
Thanks.
Then you would first concatenate the pay scale to the HR data table before joining it to the employee table. something like this:
// Load the ID’s for all employees that are in
// management and concatenate the table with the
// corporate HR table.
CONCATENATE ([HR Data])
LOAD
[Person ID],
10 as [Pay scale]
RESIDENT Employees
WHERE Department = ‘Management’
Hope this helps.
Hola,
enhorabuena por el artículo. De interés y muy completo. Parece sencillo lo que hace qlikview pero es impresionante!
Un saludo
How would i go about using another tables fields in a seperate table, for example.
DATA table;
usd ammount,
(usd ammount / Zar) as ConvUSD
zar ammount,
Cur Table:
Zar,
Usd,
Hope you get the idea.. thanx
Very helpfull Barry. Thank you very much!
Great article, thanks!
One thing which is still confusing me a bit is the use of drop tables, resident, etc. Could you perhaps write an article on the use of that?
Thanks