Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

4 Replies
bgerchikov
Contributor III

Re: Joining Multiple table,

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!

Not applicable

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;

bgerchikov
Contributor III

Re: Joining Multiple table,

Hi Suryan,

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

Good luck

chauhans85
Esteemed Contributor

Re: Joining Multiple table,

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

Community Browser