Explaining Joins

Those familier with manipulating data in SQL or other BI technologies should have no problem understanding the different types of joins available in QlikView. But those who aren't sometimes need a little explanation and it's a topic that I've fond myself explaining often when training people.

The four primary logical join types, LEFT, RIGHT, INNER and OUTER are supported in QlikView. Let's consider the following eample tables and what the output for each will be:

Field1	Field2
A	1
B	2
C	3

Field2	Field3
2	X
3	Y
4	Z

As you can see, both tables contain a column called Field2 for which some, but not all, of the values have a matching entry in the other table. The syntax for performing a join in QlikView is relatively simple as follows:

MyTable:
LOAD
    Field1,
    Field2
FROM myqvd1.qvd (qvd);

LEFT JOIN (MyTable)
LOAD
    Field2,
    Field3
FROM myqvd2.qvd (qvd);

It is important to note that QlikView links records based on matching field names in the two tables being joined. In the example above, both tables contain a field called Field2 and thus QlikView will look for matches across these fields.

Also worth noting is the the table name defined in curved brackets. This defines the previously loaded table which the following table should be joined to. It is not mandetory but it is considered best practice to always specify a table to join to inorder to avoid confusion or mistakes. Without it, QlikView joins the table of data to the previously loaded table.

LEFT JOIN

In a LEFT join, all the records from the first table are kept, and only those records from the second table that match a record in the first table are kept. Using the above example tables, the output would be as follows:

Field1	Field2	Field3
A	1
B	2	X
C	3	Y

You will notice that there is a blank entry in Field3. This is because no matching record exists in the second table. You will also notice that the record in the second table for which there is no match in the first table has not been included.

RIGHT JOIN

In a RIGHT join, all the records from the second table are kept, and only those records from the first table that match a record in the second table are kept. Using the above example tables, the output would be as follows:

Field1	Field2	Field3
B	2	X
C	3	Y
	4	Z

You will notice that there is a blank entry in Field1. This is because no matching record exists in the first table. You will also notice that the record in the first table for which there is no match in the second table has not been included.

INNER JOIN

In an INNER join, only records with a match across both tables will be kept. Using the above example tables, the output would be as follows:

Field1	Field2	Field3
B	2	X
C	3	Y

You will notice that there are no blank entries. Records from either table for which a match was not found have not been included.

OUTER JOIN

In an OUTER join, All records will be kept and where possible, records will be matched. Using the above example tables, the output would be as follows:

Field1	Field2	Field3
A	1
B	2	X
C	3	Y
	4	Z

You will notice that there are blank entries in both Field1 and Field3. This is because a matching record was not found in the other table.

When Joins Go Wrong

The most common and easily made mistake when joining tables is not having at least one field named the same in both tables. When this occurs, QlikView can't make any matches within the data and so outputs all possible combinations of the records in both tables. This is known as the cartesian product. If we take the above example but rename Field2 in the second table by mistake so it no longer matches the first table like this:

Field1	Field2
A	1
B	2
C	3

Field22	Field3
2	X
3	Y
4	Z

When performing any of the join types on these two tables, QlikView would create the following result:

Field1	Field2	Field22	Field3
A	1	2	X
A	1	3	Y
A	1	4	Z
B	2	2	X
B	2	3	Y
B	2	4	Z
C	3	2	X
C	3	3	Y
C	3	4	Z

The produced table contains all possible combinations and therefor the number of rows it contains will be the number of records in the two tables multiplied together. In this example 3 x 3 = 9 records. This isn't the end of the world when data sizes are relatively small, but when mistakes are made joining tables containing millions of records, the resulting table can take a very long time to calculate and be so large that it uses up all the physical RAM and result in the machine becoming unresponsive. Anyone working with QlikView on large datasets for very long will have learnt this lesson the hard way. I remember making a mistake whilst joinging a billion record table to itself and had to explain red faced to the customer's IT department why I needed them to restart the non-responsive server as it tried to calculate the result.

A cartesian product isn't always the result of a mistake though and can also be performed on purpose in advanced data transformations. For example, if I have a calendar table with a record for each day but what I really need is to expand it to have a record for each 8 hour shift in each day, there is more than one way to approach the problem. I could load the calendar into a temp table and then concatenate it 3 times into a resulting table denoting the shift each time, or I could create a Shifts table containing a record for each shift and join it to the calendar table providing no matching field and thus purposfully causing a cartesian product.