topic Re: Join three tables in QlikView

Join three tables

Kunkulis — Sat, 16 Nov 2024 02:34:59 GMT

Hello,

I am Loading three tables.

One of the table works as a bridge.

So Table 1 will have 2 columns - Table1.ID and Table1.Name;
Table 2 will have 2 columns - Table2.ID and Table2.Name;
Table 3 is the bridge with two columns - Table1.ID and Table2.ID;

So My goal is to have 1 table that has the information of Table1.Name and Table2.Name

Re: Join three tables

Miguel_Angel_Baeyens — Tue, 03 Sep 2019 12:45:59 GMT

Assuming that the values in Table1.ID and Table2.ID are identical and don't require any further modification, rename the fields in the script and that will do:

BigTable: // The result of the join

LOAD ID,

Name

FROM Table1; // specify the source here

JOIN

LOAD ID,

NAME

FROM Table2; // specify the source here

The resulting table will have all values for ID and Name from both tables. You can of course apply LEFT JOIN or RIGHT JOIN depending on your needs.

Re: Join three tables

Kunkulis — Tue, 03 Sep 2019 13:02:33 GMT

Hi,
No, the Table 1 ID isn't the same as the Table 2 ID. The only thing that joins those two table IDs is the Bridge table, which has the Table1.ID that correlates with Table2.ID

Table 1

Name

Table2

Name

Bridge

Table1.ID

Table2.ID

And the end goal I am seeking is to have is:

End

Table1.Name

Table2.Name

Re: Join three tables

Miguel_Angel_Baeyens — Tue, 03 Sep 2019 13:50:12 GMT

I will then suggest to use ApplyMap() if the values in the table Bridge are unique in addition to JOIN.

BridgeMap: // Mapping table, this table will be removed from the data model once the script is executed

MAPPING LOAD 

Table1.ID

Table2.ID

FROM Bridge;


End:

LOAD ApplyMap('BridgeMap', ID, 'N/A') AS ID // 'N/A' is not required, but it helps troubleshooting data quality

    // Field ID must keep the same name for the JOIN to succeed

Name AS Table1.Name

FROM Table1;

JOIN // consider using LEFT JOIN OR RIGHT JOIN if required

LOAD ApplyMap('BridgeMap', ID, 'N/A2') AS ID

Name AS Table2.Name

FROM Table2;


DROP FIELD ID; // If not needed, the field can be removed from the "End" table

Re: Join three tables

Brett_Bleess — Thu, 12 Sep 2019 20:45:02 GMT

Alvis, I am attaching a Design Blog post along the lines of Miguel's post that may be of some further help to you. If Miguel's post did help you get things figured out, please be sure to use the Accept as Solution button to give him credit and let others know that worked for your issue. If not, have a look at the post below, it is further information on Apply Map versus Join...

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

Regards,
Brett