Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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;
Hi Suryan,
Your LOAD statement following SQL SELECT is not correct. Just remove and see what happens.
Good luck
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