Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kunkulis
Creator
Creator

Join three tables

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

 

Labels (3)
4 Replies
Miguel_Angel_Baeyens

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.

Kunkulis
Creator
Creator
Author

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
ID
Name

 

Table2
ID
Name

 

Bridge
Table1.ID
Table2.ID

 

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

End
Table1.Name
Table2.Name

 

Miguel_Angel_Baeyens

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
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.