QlikView Left Join

Introduction to QlikView Left Join

Join is an operation that is used to merge two tables into a single table. Generally, the data that we use to analyze in QlikView will be a combination of multiple data sources which will be in the form of different tables. When these tables have a common field (the field that can be used to identify a record uniquely) then that field can be used to join the two tables into one virtual table. The resulting table is a horizontal extension of the current table by adding columns to it from the second table.

How to Use Left Join in QlikView?

In QlikView, the Joins are defined at the script level. The tables that are already available in the script can only be joined. Once the tables are joined, QlikView will not see the individual tables but it will only work only on the joined tables. This will be in the form of a single internal table that will be saved in QlikView. Before using a Join, we need to consider a few points as follows.

• The resultant table is usually large in size, which may lead to a slowdown in the performance of the dashboard.
• There are chances of data loss, the number of records in the original table may not be similar to the resultant table.
• The tables can be joined only when one or more fields are in common in the two tables.
• It is recommended to have the same field name as the common key fields that will be used to join.
• The number of common fields that we will require to join is based on our personal choice.
• The fields that are joined must be of the same data type.

Before we look into the Left Join in detail, let’s understand all the types of joins available. These joining concepts are the same as the ones used in SQL.

1. Inner Join: The Inner Join is used to merge and display only the common rows available in the Left and Right table.
2. Outer Join: The Outer Join is used to merge and display all the rows that are available in Left and Right table. Wherever there are no common fields, there will be Null values for the unmerged rows.
3. Right Join: The Right Join is used to merge and display all rows from the Right table and only the matching or common rows from the Left table.
4. Left Join: The same logic applies for the Left Join where all rows from the Left table are displayed and only the matching or common rows from the Right table are displayed.

Let’s look at an example of two tables and how the resultant table will look like when a Left Join is applied. Below are two sample data sources: Sales data and Sales Volume data.

Sales Data
Market	Sales (In Millions)
Italy	20
Germany	45
France	22
Austria	10
Finland	12
Ireland	9

 

Sales Volume
Market	Vehicles Sold
Britain	40000
Italy	20000
Austria	2000
Finland	3000
Romania	4000
Portugal	1000

Output:

Left Join Output
Market	Sales (in Millions)	Vehicles Sold
Italy	20	20000
Germany	45	Null
France	22	Null
Austria	10	2000
Finland	12	3000
Ireland	9	Null

We will notice in the above output that all records from the ‘Sales Data’ which is the left table is displayed and only matching or common rows from the ‘Sales Volume’ table is displayed. Wherever there are no common fields, there will be Null values.

Examples of QlikView Left Join

Below are the examples of QlikView Left Join:

Example #1

Open QlikView and load the first table using the script editor and name the table. The script will look something like below.

Code:

Sales_Data:
LOAD Market,
[Sales (in millions)] FROM
[C:\Users\smoham87\Desktop\Sales Data.xlsx] (ooxml, embedded labels, table is [Sales Data]);

Example #2

Load the second table using the script editor and name the table with a different name. The script will now look like below.

Code:

Sales_Data:
LOAD Market,
[Sales (in millions)] FROM
[C:\Users\smoham87\Desktop\Sales Data.xlsx] (ooxml, embedded labels, table is [Sales Data]);
Sales_Volume:
LOAD Market,
[Vehicles sold] FROM
[C:\Users\smoham87\Desktop\Sales Volume.xlsx] (ooxml, embedded labels, table is [Sales Volume]);

Example #3

Add the Left Join script in between the two tables in the script editor window as below.

Code:

Sales_Data:
LOAD Market,
[Sales (in millions)] FROM
[C:\Users\smoham87\Desktop\Sales Data.xlsx] (ooxml, embedded labels, table is [Sales Data]);
LEFT JOIN(Sales_Data)
Sales_Volume:
LOAD Market,
[Vehicles sold] FROM
[C:\Users\smoham87\Desktop\Sales Volume.xlsx] (ooxml, embedded labels, table is [Sales Volume]);

Click OK on the script editor window and reload the script.

Example #4

Let’s create a Table Box by adding all the fields from both tables to display the output of the Join. The Table Box will look something like below.

We will find that in the above Table Box all records from the ‘Sales Data’ which is the left table is displayed and only matching or common rows from the ‘Sales Volume’ table is displayed.

How does the Join function differ from the Concatenate function in QlikView?:

The Join function is used to combine data rows from multiple tables. It uses a common field to merge similar rows from two tables into one, whereas the concatenate table is used to append two tables. The concatenate function will work without a common field. It will simply append the two tables even if the rows are repeating. There are no conditions that need to be satisfied for the concatenate to take place, whereas the join will work only when the two tables have a common field.

How does the Join Function Differ from Keep Function in QlikView?:

The Join function will combine two tables into one table using a common field whereas, the Keep function will use the same logic as the Join function but it will maintain the two tables separately.

Conclusion

In most scenarios, we will have the need to work with one or more data sources. We can bring meaningful insights from these data sources only when they are merged logically. Hence, Data Joining is an important function to create these merges using a common field from both the tables.

Recommended Articles

This is a guide to QlikView Left Join. Here we discuss how to use the Left Join function in QlikView and its Examples with code implementation. You can also go through our suggested articles to learn more –

1. Introduction to QlikView Functions
2. Tableau vs QlikView – Top Differences
3. Benefits of Using QlikView Charts
4. Top 10 QlikView Interview Questions
5. Complete Guide to QlikView Architecture