How to join Multiple tables in Qlik Sense

[tvisha]

Hi,

I have 4-5 tables. I want to join each other.

The first three courses, organization, and registrations are linked together by courseid and orgid.

The fourth is linked by hrid. 

How do I link the table hr_positions and department as they are not linked to tblcourses but linked to human_resources table? 

Qualify*;

tblCourses:
LOAD "COURSE_ID" as CourseId,
    DESCRIPTION,   
    "ORG_ID" as OrgId,

SQL SELECT  *
FROM COURSES
 where COURSE_TYPE='W';

left join (tblCourses)
LOAD  "ORG_ID" as OrgId,
    "ORG_NAME",

SQL SELECT *
FROM  ORGANISATIONS;

left Join (tblCourses)
LOAD "COURSE_ID" as CourseId,
        "HR_ID" as HRID;

SQL SELECT *
FROM  REGISTRATIONS;

HUMAN_RESOURCES:
left join (tblCourses) 
LOAD "HR_ID" as HRID,

SQL SELECT *
FROM "HUMAN_RESOURCES";
Left join (tblcourses) --- How do I join HRID to above registration table

LOAD "pid" as Pid,
    "hr_id" as HRID;

SQL SELECT *
FROM hr_positions;
left join (tblCourses)

 LOAD "pid" as pid,
Description,

SQL SELECT *
FROM department;

[Digvijay_Singh]

Can you share complete script, if possible a sample app would help community members to get into the real issue faster.

[tvisha]

I even tried this, but it is not working,

Table1:

LOAD "HR_ID" as HRID,
    "HR_TYPE",
    SURNAME,
    "FULL_NAME";

SQL SELECT *
FROM  “HUMAN_RESOURCES";   

Inner Join (Table1)

Table2:

LOAD "hr_id" as HRID ,
  "position_id" as PositionId;   

SQL SELECT *
FROM Department;

Inner Join (Table2)

LOAD "POSITION_ID" as PositionId,
    "LEVEL_3" as Department;

SQL SELECT *
FROM "POSITIONS";

[tvisha]

okay, I will try to create a dummy data and see

[Digvijay_Singh]

Try this may be, no need to define table2 :

Inner Join (Table1)

LOAD "hr_id" as HRID ,
  "position_id" as PositionId;   

SQL SELECT *
FROM Department;


Inner Join (Table1)

LOAD "POSITION_ID" as PositionId,
    "LEVEL_3" as Department;

SQL SELECT *
FROM "POSITIONS";

[tvisha]

Sorry for the delay in replying.

Here is a sample script, which I now created. simple tables with simple data, so now by default Qlik should join, but when I pull it into the table, it does not.

///////////////////////
LOAD department_ID,
department;

[department]:
SELECT *
FROM "TestQuery_Database".dbo.department;

///////////////////////
LOAD HR_ID as [HR_ID],
START_DATE;

[EMPLOYEES_T]:
SELECT *
FROM "TestQuery_Database".dbo."EMPLOYEES_T";

///////////////////////
LOAD HR_ID as [HR_ID],
SURNAME,
FIRST_NAME,
MIDDLE_NAME;

[HUMAN_RESOURCES]:
SELECT *
FROM "TestQuery_Database".dbo."HUMAN_RESOURCES";

///////////////////////
LOAD department_ID,
hr_id as [HR_ID];

[Reference_Dept_Hr]:
SELECT *
FROM "TestQuery_Database".dbo."Reference_Dept_Hr";

I am not sure how to attach the app or data.

[tvisha]

Looking for an answer from experts please

[Digvijay_Singh]

Can you share your data model image, I am not sure why do you have table name after the load statement, shouldn't it be like this sequence in all the tables - 

//This below table name  should come before the load statement, haven't actually tested the impact of this having in the middle in your script

[department]:    

LOAD department_ID,
department;

SELECT *
FROM "TestQuery_Database".dbo.department;

[tvisha]

Sorry, there was some typo, here is the updated one.

///////////////////////
[department]:

LOAD department_ID,
department;


SELECT *
FROM "TestQuery_Database".dbo.department;

///////////////////////
[EMPLOYEES_T]:

LOAD HR_ID as [HR_ID],
START_DATE;

SELECT *
FROM "TestQuery_Database".dbo."EMPLOYEES_T";

///////////////////////
[HUMAN_RESOURCES]:

LOAD HR_ID as [HR_ID],
SURNAME,
FIRST_NAME,
MIDDLE_NAME;

SELECT *
FROM "TestQuery_Database".dbo."HUMAN_RESOURCES";

///////////////////////
[Reference_Dept_Hr]:

LOAD department_ID,
hr_id as [HR_ID];

SELECT *
FROM "TestQuery_Database".dbo."Reference_Dept_Hr";

[tvisha]

Data Model image. (I have created this sample data model)

[Digvijay_Singh · Accepted solution]

The joining looks okay in your data model view, looks like something is wrong with the keyfield values.

Can you create new field HR_ID1 in the table as shown below, and share the values of it, I want to know why this table ref-dept-hr table values are not associating with the HUMAN_RESOURCES table.