Joining Multiple table,

[#1]

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

Table HR Person Dtl
person_id	dept	Emp Group
1	health	Admin
2	Health	Admin
3	Health Care	Professional
4	IT	Professional
5	Accounts	Admin

Table  Tean
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

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

[bgerchikov]

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!

[#3]

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]

Hi Suryan,

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

Good luck

[SunilChauhan]

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