4 Replies Latest reply: Aug 18, 2012 4:07 AM by Sunil Chauhan RSS

    Joining Multiple table,

      Can someone help me with joining multiple table.  This is how the records in the two table will look.

       

      Table HR Person Dtl
      person_iddeptEmp Group
      1healthAdmin
      2HealthAdmin
      3Health CareProfessional
      4ITProfessional
      5AccountsAdmin
      Table  Tean
      hr_person_idteam 1role
      1obesityMember
      1NursingTeam Director
      1CoverageProgram Office
      2obesityTeam Director
      3CoverageTeam Director
      3obesityMember

       

      I want to generate some HR benefit charts which should reflect how much we spend for each team and how much for the role.

      Also want to generate charts which should reflect employee percentage in each team

        • Re: Joining Multiple table,
          Boris Gerchikov

          Hi Suryan,

           

          It looks like you need some project to do:

           

          //Load raw data first

           

          HRPersonDtl:

          LOAD * INLINE [

              person_id,          dept,          Emp Group

              1, Health, Admin

              2,          Health,          Admin

              3, Health, Care Professional

          ];

           

           

          Team:

          LOAD * INLINE [

          hr_person_id,          team 1,          role

          1,          obesity,          Member

          1,          Nursing,          Team Director

          1,          Coverage,          Program Office

          2,          obesity,          Team Director

          3,          Coverage,          Team Director

          3,          obesity,          Member

          ];

           

           

          //Build the logical model

           

           

          left join (Team)

          LOAD person_id as hr_person_id, dept,          [Emp Group]

          Resident HRPersonDtl;

           

           

          DROP table HRPersonDtl;

           

           

          Add benefit data, calendar and start working on charts

           

          Good luck!

            • Re: Joining Multiple table,

              Thanks Boris,

              I'm new to qlik and not sure, can you clarify further on the points below.

               

              1. These two sample data I have given is coming from a Oracle Database Table.

              2. I tried the following and gives error.

               

              ODBC  CONNECT to xxx (user xxx passwd xxx);

               

              TestTable1:SQL SELECT
                     emp_person_id,
                      emp_employee_number,
                      emp_full_name,
                      emp_first_name,
                      emp_middle_names,
                      emp_last_name,
                      emp_known_as,
                      emp_person_type,
                      emp_hire_date,
                      emp_original_date_of_hire,
                      emp_no_yr_svc_frm_hire_dt,
                      emp_no_yr_svc_frm_org_hire_dt,
                      emp_adjusted_svc_date,
                     to_char(emp_adjusted_svc_date,'yyyy')as Service_Date_Year
              FROM APPS."RWJF_HR_DATA_VISUAL_1";LOAD
                    
              emp_person_id,
                     
              emp_employee_number,
                     
              emp_full_name,
                     
              emp_first_name,
                     
              emp_middle_names,
                     
              emp_last_name,
                     
              emp_known_as,
                     
              emp_person_type,
                     
              emp_hire_date,
                     
              emp_original_date_of_hire,
                     
              emp_no_yr_svc_frm_hire_dt,
                     
              emp_no_yr_svc_frm_org_hire_dt,
                     
              emp_adjusted_svc_date,
                    
              Service_Date_Year
              from  TestTable1;  

               

              SQL SELECT
                   parent_box_id,
                   box_id,
                   box_title,
                   record_type,
                   id,
                   firstname,
                   middlename,
                   role_job_title,
                   box_sequence_number,
                   photo,
                   phone_ext,
                   dept_team_id,
                   dept_team_name,
                   email,
                   start_date,
                   assist,
                   alsoin,
                   hr_person_id as person_id,
                   hr_person_id,
                   sort_id
              FROM  rwjf_team_chart@PUBDB_HR_XFER.rwjf.org;

               

                • Re: Joining Multiple table,
                  Boris Gerchikov

                  Hi Suryan,

                   

                  Your LOAD statement following SQL SELECT is not correct. Just remove and see what happens.

                   

                  Good luck

                  • Re: Joining Multiple table,
                    Sunil Chauhan

                    ODBC  CONNECT to xxx (user xxx passwd xxx);

                    qualify *;

                    unqualify  person_id;


                    TestTable1:

                    SQL SELECT
                           emp_person_id,
                            emp_employee_number,
                            emp_full_name,
                            emp_first_name,
                            emp_middle_names,
                            emp_last_name,
                            emp_known_as,
                            emp_person_type,
                            emp_hire_date,
                            emp_original_date_of_hire,
                            emp_no_yr_svc_frm_hire_dt,
                            emp_no_yr_svc_frm_org_hire_dt,
                            emp_adjusted_svc_date,
                           to_char(emp_adjusted_svc_date,'yyyy')as Service_Date_Year
                    FROM APPS."RWJF_HR_DATA_VISUAL_1";

                     

                    SQL SELECT
                         parent_box_id,
                         box_id,
                         box_title,
                         record_type,
                         id,
                         firstname,
                         middlename,
                         role_job_title,
                         box_sequence_number,
                         photo,
                         phone_ext,
                         dept_team_id,
                         dept_team_name,
                         email,
                         start_date,
                         assist,
                         alsoin,
                        hr_person_id as person_id,
                         hr_person_id,
                         sort_id
                    FROM  rwjf_team_chart@PUBDB_HR_XFER.rwjf.org;

                     

                     

                    hope this helps