Beginners Task Series - Task6(Self Join)

                                                                    Self Join in Qlik..

     

    All Beginners,

     

    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.

     

    Scenario:

    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 1Header 2Header 3Header 4Header 5Header 6Header 7
    Emp_IDFirst_NameLast_NameJob_IDManager_IDDateofJoinDeptID

     

     

    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 !...

     

    Step 1:

    Load Emp table

     

    Step 2:

    Load a Resident of Emp table

     

    Step 3:

    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)

     

    Step 4:

    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

    Emp:

    LOAD JOB_ID,

         EMPLOYEE_ID,

         FIRST_NAME,

         LAST_NAME,

         EMAIL,

         DEPARTMENT_ID,

         HIRE_DATE,

         COMMISSION_PCT,

         MANAGER_ID,

         PHONE_NUMBER,

         SALARY

    FROM

    emp data.xls

    (biff, embedded labels, table is Sheet1$);

     

     

    Join  // Cartesian product                  // Step 3

     

     

    Mgr:                                                // Step 2

    Load *

    Resident Emp;

     

     

    // Apply conditions on the joined table

    UNQUALIFY *;

     

     

    Temp:

    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

    Resident Emp

    Where Emp.MANAGER_ID = Mgr.Emp.EMPLOYEE_ID

    Order by Emp.EMPLOYEE_ID;

     

     

    Drop table Emp;

     

    Limitations:

    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:

    1.Beginners Tasks Series - Task1

    2.Beginners Tasks Series - Task2

    3.Beginners Task Series - Task3(Interval Match)

    4.Beginners Task Series - Task4

    5.Beginners Task Series - Task5


     

     

     

     

    Thanks

    Kiran Kumar