topic Re: Multiple tables joining in App Development

How to join Multiple tables in Qlik Sense

tvisha — Sat, 17 Sep 2022 08:05:43 GMT

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;

Re: Multiple tables joining

Digvijay_Singh — Tue, 08 Feb 2022 16:20:54 GMT

Why do you need to join them? You may keep them separate and associated with the remaining tables using the IDs you got.

Re: Multiple tables joining

tvisha — Tue, 08 Feb 2022 16:22:58 GMT

What do you mean by associate here? Is it not like joining?

Re: Multiple tables joining

Digvijay_Singh — Tue, 08 Feb 2022 16:29:58 GMT

We keep key field name same in two tables to associate them. We don't need to join them in the script if we don't need to. Qlik manages the joins in the charts during runtime if we have chart fields coming from different tables.

Re: Multiple tables joining

tvisha — Tue, 08 Feb 2022 16:34:58 GMT

Thank You

Re: Multiple tables joining

tvisha — Tue, 08 Feb 2022 16:42:25 GMT

one question, but when it auto joins, it would be always a inner join. If we have to do left or right or full then?

Re: Multiple tables joining

Digvijay_Singh — Tue, 08 Feb 2022 16:50:51 GMT

It won't be inner join, it would be left or right based on how do we sequence our dimensions.

Re: How to join Multiple tables in Qlik Sense

tvisha — Sat, 17 Sep 2022 08:06:48 GMT

Thank you for your reply.

Somehow, the last 2 are not working properly.

When I join these 2 tables, it is doing the right outer join with the positions table, whereas it needs to be the inner join. Anything wrong with my statements

Qualify*; LOAD "HR_ID" as HRID, "HR_TYPE", SURNAME, "FULL_NAME"; SQL SELECT * FROM HUMAN_RESOURCES"; LOAD "hr_id" as HRID , "position_id" as PositionId; SQL SELECT * FROM Positions Position id is number(9,0) Hr_id is varchar(10)

Re: Multiple tables joining

Digvijay_Singh — Wed, 09 Feb 2022 15:43:03 GMT

Inner join needs to be explicitly done in the script, any issue in this below? I think after this Human resources and position table will have records for the matching HRIDs only.

Table1:

LOAD "HR_ID" as HRID,

"HR_TYPE",

SURNAME,

"FULL_NAME";

SQL SELECT *

FROM HUMAN_RESOURCES";

Inner Join(Table1)

LOAD "hr_id" as HRID ,

"position_id" as PositionId;

SQL SELECT *

FROM Positions

Re: Multiple tables joining

tvisha — Wed, 09 Feb 2022 15:52:20 GMT

Unfortunately it is not working

Re: Multiple tables joining

Digvijay_Singh — Wed, 09 Feb 2022 15:57:47 GMT

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

Re: How to join Multiple tables in Qlik Sense

tvisha — Sat, 17 Sep 2022 08:07:45 GMT

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";

Re: Multiple tables joining

tvisha — Wed, 09 Feb 2022 16:00:39 GMT

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

Re: Multiple tables joining

Digvijay_Singh — Sat, 17 Sep 2022 08:08:18 GMT

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";

Re: How to join Multiple tables in Qlik Sense

tvisha — Sat, 17 Sep 2022 08:08:58 GMT

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.

Re: Multiple tables joining

tvisha — Tue, 08 Mar 2022 09:38:47 GMT

Looking for an answer from experts please

Re: How to join Multiple tables in Qlik Sense

Digvijay_Singh — Sat, 17 Sep 2022 08:09:50 GMT

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;

Re: How to join Multiple tables in Qlik Sense

tvisha — Sat, 17 Sep 2022 08:09:24 GMT

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";

Re: Multiple tables joining

tvisha — Wed, 09 Mar 2022 10:26:16 GMT

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

Re: Multiple tables joining

Digvijay_Singh — Wed, 09 Mar 2022 16:42:15 GMT

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.