topic Re: Joining Multiple table, in QlikView

Joining Multiple table,

Thu, 16 Aug 2012 18:42:15 GMT

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

Re: Joining Multiple table,

bgerchikov — Fri, 17 Aug 2012 03:32:06 GMT

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,

Fri, 17 Aug 2012 15:45:41 GMT

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,

bgerchikov — Fri, 17 Aug 2012 16:05:31 GMT

Hi Suryan,

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

Good luck

Re: Joining Multiple table,

SunilChauhan — Sat, 18 Aug 2012 08:07:37 GMT

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