QlikView - Joins

Advertisements

Previous Page
Next Page  

Joins in QlikView are used to combine data from two data sets into one. Joins in QlikView mean the same as in joins in SQL. Only the column and row values that match the join conditions are shown in the output. In case you are completely new to joins, you may like to first learn about them here.

Input Data

Let us consider the following two CSV data files, which are used as input for further illustrations.

Product List:
ProductID,ProductCategory
1,Outdoor Recreation
2,Clothing
3,Costumes & Accessories
4,Athletics
5,Personal Care
6,Hobbies & Creative Arts

ProductSales:
ProductID,ProductCategory,SaleAmount
4,Athletics,1212
5,Personal Care,5211
6,Hobbies & Creative Arts,1021
7,Display Board,2177
8,Game,1145
9,soap,1012
10,Beverages & Tobacco,2514

Inner Join

We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. Then we edit the commands in the script to create an inner join between the tables.

load_script

Inner join fetches only those rows, which are present in both the tables. In this case, the rows available in both Product List and Product Sales table are fetched. We create a Table Box using the menu Layout → New Sheet Objects → Table Box where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.

inner_join

Left Join

Left join involves fetching all the rows from the table in the left and the matching rows from the table in the right.

Load Script

Sales:
LOAD ProductID, 
     ProductCategory, 
     SaleAmount
FROM
[C:\Qlikview\data\product_lists.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LEFT JOIN(Sales)

LOAD ProductID, 
     ProductCategory
FROM
[C:\Qlikview\data\Productsales.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

We create a Table Box using the menu Layout → New Sheet Objects → Table Box, where we choose all the three fields − ProductID, ProductCategory and SaleAmount to be displayed.

left_join

Right Join

Right join involves fetching all the rows from the table in the right and the matching rows from the table in the left.

Load Script

Sales:
LOAD ProductID, 
     ProductCategory, 
     SaleAmount
FROM
[C:\Qlikview\data\product_lists.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

RIGHT JOIN(Sales)

LOAD ProductID, 
     ProductCategory
FROM
[C:\Qlikview\data\Productsales.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

We create a Table Box using the menu Layout → New Sheet Objects → Table Box, where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.

right_join

Outer Join

Outer join involves fetching all the rows from the table in the right as well as from the table in the left.

Load Script

Sales:
LOAD ProductID, 
     ProductCategory, 
     SaleAmount
FROM
[C:\Qlikview\data\product_lists.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

OUTER JOIN(Sales)

LOAD ProductID, 
     ProductCategory
FROM
[C:\Qlikview\data\Productsales.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

We create a Table Box using the menu Layout → New Sheet Objects → Table Box where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.

outer_join
Advertisements