QlikView Join Functions – Types & Practical Examples of Joins

In our last session, we understood the QlikView system requirements. Today, we will start our new chapter called QlikView Join Tutorial, in which we start with the definition of QlikView Join function. Moreover, we will discuss the different types of Joins in QlikView: Left, Right, Inner, Outer join with examples.

So, let’s start QlikView Join with examples.

QlikView Join Functions – Types & Practical Examples of Joins

What is QlikView Joins?

The function ‘Join’ is used to join or merge data records from two or more tables. It is different from concatenating function as the join function merges or combines similar rows in two tables into one row while concatenating function simple joins two tables even if the rows are repetitive.

The resultant table after applying QlikView joins function depends upon what condition or what kind of join clause has been specified in the script. Only the rows and column which satisfy the conditions are displayed.

Do you know How to Create QlikView List & Multi Box?

QlikView Join – Types & Practical Example

There are four different kinds of QlikView Join functions through which you can decide the way in which the tables will merge their information into one. To show you how and where to apply Join in QlikView, we have taken two sample data (CSV) files. One showing the product ID and product type and another file having product name of certain data entries.

QlikView Join Example-

INPUT DATA- Product list

Product_Id,Product_Type

pr1, Sporting Goods

pr2, “Food, Beverages & Tobacco”

pr3, Apparel & Accessories

pr4, Sporting Goods

pr5,Health & Beauty

pr6,Arts & Entertainment

pr7,Arts & Entertainment

pr8,Arts & Entertainment

Let’s explore the DIfference Between Power BI and QlikView

Product Name:

Product_Id,Product_Type,Product_name

pr5,Health & Beauty,Body Lotion

pr6,Arts & Entertainment,Musical Instruments

pr7, Arts & Entertainment, Orchestra Accessories

pr8,Arts & Entertainment,Crafting Materials

pr9, Hardware, Power Tool Batteries

pr10,Home & Garden,Bath Caddies

pr11,”Food, Beverages & Tobacco”,Frozen Vegetables

pr12,Home & Garden,Power Equipment

pr13, Home & Garden, Water Hose

pr14,Health & Beauty,Aloevera Gel

pr15,Health & Beauty,Face pack

In the next step, we have loaded these two data files into memory and will apply different QlikView Join functions on this script now.

a. QlikView Inner Join

The inner join in QlikView will merge and display only those rows which are common in both the tables (left and right). Apply Inner Join in the script editor. 

Name:
LOAD Product_Id,
     Product_Type,
     Product_name
FROM
[C:\Users\HP\Desktop\Dataflair\productname.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

INNER JOIN(Name)
LOAD Product_Id,
     Product_Type,
FROM
[C:\Users\HP\Desktop\Dataflair\Product List.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

QlikView Join Functions – QlikView Inner Join

Save and reload the script then create a table box. In the properties window, you will have all the fields with the common data values. In our sample files, upon creating a table box, three fields-product ID, type and name will be shown. The final table box shows rows of ID 5 to 8 because they are common in both tables.

Do you know what is QlikView Database Connection & Inline data?

QlikView Join Functions – QlikView Inner Join

b. QlikView Left Join

While talking about joining feature, there is terminology used- Left and Right tables. The left table in our sample in the table ‘Productname’ and the right table is ‘Productlist’.

Name:
LOAD Product_Id,
     Product_Type,
     Product_name
FROM
[C:\Users\HP\Desktop\Dataflair\productname.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LEFT JOIN(Name)
LOAD Product_Id,
     Product_Type,
FROM
[C:\Users\HP\Desktop\Dataflair\Product List.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

QlikView Join Functions – QlikView Left Join

The Left Join function in QlikView, keeps the left table intact or in other words fetches all the information from the left table but takes only the common or matching rows from the right table. To understand better, refer to the screenshots attached below. 

QlikView Join Functions – QlikView Left Join

c. QlikView Right Join

Applying the same logic as in the left join. QlikView Right Join function fetches all the rows from the right table but only the common or matching rows from the left table.

Name:
LOAD Product_Id,
     Product_Type,
     Product_name
FROM
[C:\Users\HP\Desktop\Dataflair\productname.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

RIGHT JOIN(Name)
LOAD Product_Id,
     Product_Type,
FROM
[C:\Users\HP\Desktop\Dataflair\Product List.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

QlikView Join Functions – QlikView Right Join

As you can see in the system screenshots below, the column under the field name ‘Product name’ is left empty in front of the values for which the relevant information is not available and it displays the product name where it is available.

Let’s explore the QlikView Careers Opportunities and Jobs Trends

d. QlikView Outer Join

The QlikView Outer Join merges all the rows from both the left and right tables showing one complete table.

Name:
LOAD Product_Id,
     Product_Type,
     Product_name
FROM
[C:\Users\HP\Desktop\Dataflair\productname.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

OUTER JOIN(Name)
LOAD Product_Id,
     Product_Type,
FROM
[C:\Users\HP\Desktop\Dataflair\Product List.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

QlikView Join Functions – QlikView Outer Join

QlikView Join Functions – QlikView Outer Join

So, this was all about QlikView Join Function. Hope you like our explanation.

Conclusion

Hence, QlikView join function helps in eliminating redundant data and keeping only on set of values while merging several tables. One thing to keep in mind is that only those tables which have values in common can be joined by this function.

Else, if you know well about the join function of SQL you wouldn’t have a tough time learning this as both are fundamentally similar. Furthermore, if we miss something or you wanna ask query? Feel free to share with us!

Related Topic – QlikView Dashboard

For Reference