The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are important differences that should be understood.
Examine the sample tables below. Note that they share one common field name, “Key”. Also note that Table1 has a Key value “3” that is not present in Table2.
JOIN will combine rows where the Key value matches. The keyword OUTER will also retain rows that do not match rows in the other table. Here’s what the merged table will look like after an outer join.
OUTER JOIN (Table1) LOAD * RESIDENT Table2;
Values A1 and C1, which were in different tables, now occupy the same row in the result table. The row with Key 3 has missing values for C & D, because there was no matching Key in Table2.
Creating a chart that uses “Key” for dimension will produce results similar to the Table Box above.
The important point is that values with the same Key value have been merged together into a single row. If value A1 is selected, note that values C1 & D1 remain associated (white). The set A1,B1,C1,D1 is indivisible.
Now let’s look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here’s what our sample data will look like after Concatenate.
CONCATENATE (Table1) LOAD * RESIDENT Table2;
Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the “other” table.
If the data is used to build a chart that utilizes the common field “Key” as dimension, the chart looks just like the JOINed table.
Let’s make the selection “A1” in Field A and see it’s impact on our visible charts and tables.
When A1 is selected, the association to C1 & D1 is lost and C/D values become null in both the Chart and Tablebox. We cannot select both A1 and C1. This is a different result than the JOINed example.
Let’s consider a more realistic example where we may choose between JOIN and CONCATENATE. Consider the two tables below. Note that only one BudgetAmount row is present for each Region-Year combination. In the Sales table, the SalesAmount is broken down by Department within Region.
If we load both tables we can produce a chart using expressions like =Sum(BudgetAmount).
The Budget and Sales values have been summed correctly.
We then notice that we have an undesirable synthetic key, created by the Budget and Sales tables sharing the Year and Region fields. One approach to eliminate the synthetic key would be JOIN or CONCATENATE. But which one in this case?
Let’s try JOIN and see what the Chart looks like.
OUTER JOIN (Budget) LOAD * RESIDENT SALES
The summed Budget numbers are incorrect!
A look at raw data of the joined table will identify the problem. The JOIN repeated the BudgetAmount value on each Department row.
Let’s try with CONCATENATE.
CONCATENATE (Budget) LOAD * RESIDENT Sales;
The numbers are now correct and we’ve accomplished the goal of eliminating the synthetic key.
A peek at the data in the Concatenated table will make it clear why the chart is now correct. There is only BudgetAmount value or each Year-Region.
JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It’s important to understand the differences between them.