Self Join in Qlik..
Going through the edit script concepts one would definitely face joins as one of the features Qlik script has. This document is not about the types of joins rather focusing on one particular type of join i.e., self - join.
I would take the pretty old example from oracle employees table.
Show the list of employee details along with their manager_id and manager name
|Header 1||Header 2||Header 3||Header 4||Header 5||Header 6||Header 7|
Lets take the above structure , please see attached emp file for validation.
For accomplishing the above task all we require is Manager name , for which we have to use self join in database.
The Query would be like this
Select E.Emp_ID,E.First_Name, Mgr.Emp_ID, Mgr.First_Name
from Emp e, Emp mgr
Where e.Manager_ID = Mgr.Emp_ID;
How to do this in Qlik !...
Load Emp table
Load a Resident of Emp table
Use Qualify so that both tables are not concatenated automatically, then Join the both tables ( the join would be a Cartesian product of both the tables)
Load the resident of the newly joined table and apply the conditions like in the where clause
The below is the script used in the attached qvw.
Qualify *; // Step 1
(biff, embedded labels, table is Sheet1$);
Join // Cartesian product // Step 3
Mgr: // Step 2
// Apply conditions on the joined table
Load Emp.EMPLOYEE_ID as Emp_Id, Emp.FIRST_NAME as Emp_name,Mgr.Emp.EMPLOYEE_ID as Mgr_Id,Mgr.Emp.FIRST_NAME as Mgr_Name
Where Emp.MANAGER_ID = Mgr.Emp.EMPLOYEE_ID
Order by Emp.EMPLOYEE_ID;
Drop table Emp;
As I have observed if the original table contains pretty large of number of rows, third step(Join) will produce a huge number of rows and even system may hang up due to less RAM.
I request all experienced to suggest on way I have done it and any suggestions on self joins.
More for Beginners: