One of the subjects I often see new QlikView developers struggle with, especially those without prior database experience, is merging data from different tables. In this series of two posts I will explain how to use the CONCATENATE and JOIN prefixes to bring together data from multiple tables.
Simply put, the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.
Read on for the first post, which covers the concatenation of tables in more detail.
Table of contents
Concatenation
Lets consider the following case; a paper company has recently decided to move the Sales Department of their Stamford branch office into their Scranton branch office. We have been asked to merge the employee data for both branches into a single Employee table.
We start out with the following source data:
- The Scranton Employees table contains the names of the employees in the Scranton office, as well as the department they work in.
- The Stamford Employees table contains the names of the employees in the Stamford office that are in the Sales Department.
If we do a straight load of this data into QlikView and look at the generated model, we will see that both tables are loaded separately and are joined via a $Syn table. This is not the desired result, as soon as we start adding more tables to this model it will gradually devolve into a spaghetti mess. The solution is to merge the two tables using one of the following methods:
Implicit concatenation
When two tables share the exact same fields, QlikView will assume that these tables belong together and will automatically add the records from the second table to the first. In our example, both tables share two common fields: Person ID and Name. However, as the Scranton Employees table contains an extra column, Department, that is not present in the Stamford Employees table, QlikView does not automatically concatenate the two tables.
As we know that all of the employees in the Stamford Employees table are in the Sales Department, the solution is to add a Department field to the Stamford Employees table that contains the value “Sales” for each employee. Once the field is added, QlikView automatically concatenates the two tables.
Preventing implicit concatenation
What if you have two tables with the same fields, but do not want them to be concatenated? In that case you can prefix your LOAD statement with the NOCONCATENATE prefix. This prefix tells QlikView to not concatenate the data but load it to a separate table.
Click here to download the example qvw file which shows implicit concatenation and the NOCONCATENATE prefix.
Explicit concatenation
While the implicit method works, I prefer using the explicit method of concatenation because it offers two advantages over the implicit version:
- It is not based on assumption. Explicitly stating that two tables are being merged makes the script easier to read and maintain.
- It allows concatenation of tables that do not share the exact same fields.
This method works by prefixing the LOAD statement with a CONCATENATE prefix. This prefix tells QlikView to append the data to the previously loaded table. Fields that do not match between the tables are automatically filled with null values.
By default, QlikView appends the data being loaded to the previously loaded table. To add data to another table, add the name of the target table in parentheses. I recommend always specifying the target table name, as it makes the script easier to understand and maintain.
In the script below you can see how the Stamford Employees table is concatenated with the Employees table, even though they do not share all of the same fields. Also note that the target table to append to is explicitly named after the CONCATENATE prefix.
You can download the example qvw which demonstrates explicit concatenation by clicking here.
Downloads
- Implicit concatenation and NOCONCATENATE example (qvw – 189 KB)
- Explicit concatenation example (qvw – 189 KB)
- Source data (Excel- 45 KB)
Next time
This concludes the first post in this series on merging tables. In the next post I will cover the JOIN statement. In the mean time, feel free to drop your questions and comments in the comments section.
Update 2011/02/07: The second post in this series is online: click here.