Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Woohoo! Qlik Community has won “Best in Class Community” in the 2024
Khoros Kudos awards!
Using Joins in QlikView
First, you need to understand how tables are joined in QlikView. In SQL, you specify the related columns from each table to be joined. For instance, if you join a Region table to a RegionContact table, you write something similar to the following:
select
*
from
Region
join
RegionContact
on Region.RegionID = RegionContact.RegionID
In this instance, you join the Region table to the RegionContact table on the RegionID column that exists in both tables. In QlikView, a join is done based on fields that have the same name. Column names are not actually specified; if they have the same name, they join, if not, they are not joined. To join, all that is needed is the type of join.
First, there is the inner join. The definition of an inner join is that it returns all rows that match based on like fields in both tables. Here is an example:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
INNER JOIN (Region)
RegionContact:
LOAD * INLINE [
RegionID, ContactName
1,John
2,Roger
5,Linda
];
In this situation, you have a Region table with regions 1 – 4. The RegionContact table has only regions 1, 2, and 5. These tables join because the column ‘RegionID’ exists in both tables, but will only return the two matching records, 1 and 2:
Next is the left join. By definition, a left join returns all rows from the first table, and only matching rows from the second table. Here is an example:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
LEFT JOIN (Region)
RegionContact:
LOAD * INLINE [
RegionID, ContactName
1,John
2,Roger
5,Linda
];
The only difference between the left and inner join is the use of the words ‘Inner’ and ‘Left’. In this example, table 1 (Region) has Regions 1 – 4. Table 2 (RegionContact) has Regions 1, 2, and 5. Since 5 does not exist in Region, it is eliminated from the resulting data set. The Contact Name for regions that don’t exist in RegionContact will return a null value.
Opposite the left join, is the right join. By definition, the right join returns all values from the second table, and only matching values from the first table:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
RIGHT JOIN (Region)
RegionContact:
LOAD * INLINE [
RegionID, ContactName
1,John
2,Roger
5,Linda
];
Since only Regions 1, 2, and 5 exist in the second table, these are the only values that will be brought back. Since 5 does not exist in the first table, the RegionName will return a null value.
The next join to look at is the outer join. The outer join returns all rows from the first table and all rows from the second table:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
OUTER JOIN (Region)
RegionContact:
LOAD * INLINE [
RegionID, ContactName
1,John
2,Roger
5,Linda
];
This returns 5 records, records 3 and 4 will have no ContactName, and record 5 will have no RegionName.
For any of these joins, if there are no like fields to join on, they create one record for each row in each table. So if table 1 has 10 rows, and table 2 has 10 rows, and a join is performed where there is no like field name (joining field), 100 records is be returned, 1 for each combination of every value from table 1 and table 2.
The last type of join in QlikView can give developers the most trouble. Concatenate is a type of join that is similar to a SQL UNION statement. Concatenate allows rows to be placed on top of each other or merged together into one table. Let’s say we have two tables, our Region table, and another table called OldRegion:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
CONCATENATE (Region) OldRegion:
LOAD * INLINE [
RegionID, RegionName
5, SouthEast
6, NorthWest
];
In this example, you return a concatenation of these two tables:
Like-named columns are placed on top of each other. Since both column names match, you see values for all fields. Where developers run into problems is in understanding that QlikView concatenates tables that have exactly the same number of columns with exactly the same names by default (order does not matter). Consider a developer that wishes to return two tables, Region and OldRegion. They run the script above without the Concatenate:
Region:
LOAD * INLINE [
RegionID, RegionName
1,East
2,West
3,North
4,South
];
OldRegion:
LOAD * INLINE [
RegionID, RegionName
5, SouthEast
6, NorthWest
];
The developer achieves the same result as above: Only 1 table is returned. If you do not wish to create one table, the keyword ‘NOCONCATENATE’ must be specified.
Joins are very useful in QlikView as they you to simplify your data model by combining fields from like tables. It’s helpful to know your joins, and learn how to use them while developing your QlikView script.
