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.
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.
Inner join fetches only those rows, which are present in both the tables. In this case, the rows available in both the 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.
Left Join
Left join involves fetching all the rows from the table on the left and the matching rows from the table on 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.
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.
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.
