Join tables and keep the columns

[Der_Unbekante_234]

Hello together,

I have a problem and hope, that someone could help me.

I have:

Table 1: ID, Supplier name, Adress, Location, ...

Table 2: Supplier-ID, Rating, Performance, ...

I want to join Table 1 with Table 2 with the columns ID and Supplier-ID. Both contains the same ID's, but Table 2 has more ID's which are missing in Table 1.

I want to have only the ID's, which are in both tables and also want to have/keep the different columns like Performance, Rating, Supplier name, Adress and so on.

If I use 'INNER JOIN' then I have only the Supplier-ID from Table 2 and the other columns are missing then. Does someone know, how I could solve this issue?

Thanks in Advance.

[rwunderlich]

If you Inner Join using all the fields, then all fields should be retained.

Inner Join (Table1)
LOAD * Resident Table2;

-Rob

[Der_Unbekante_234]

Thanks for your answer 🙂 Yes, I want to use all fields, but how it will know, which field to match from both tables? Where I can write, that it should take the ID from Table 1 and the Supplier-ID from Table2?

And there is one more problem, which I forgot to mention. I have more tables of 1, which should be joined to Table2. Is there a possible way to do it? The tables of 1 are shown as one at the Datamanger, because they have the same columns, but are different sheets/excel files.

[rwunderlich]

Like named fields will match, you cannot specify which fieldnames to match on.  So you have a few choices.

1. Rename "ID" to "Supplier-ID" in Table1. I would think this option would be cleanest. 

2. Rename Supplier-ID to ID as you load Table2.

Inner Join (Table1)
LOAD Supplier_ID as ID, 
  Rating, Performance, ...
Resident Table2;

[Der_Unbekante_234]

Thanks for your advice. 

I have last question. If I load a file in the Data Editor, then it creates automatically this query for table 1:

FROM [lib://.........csv]

(txt, utf8, embedded lavels, delimiter is '\t', msq);

For table 2 it looks like this:

FROM [lib://.........xlsx]

(ooxml, embedded labels, table is tbl_JD_PE);

I guess it's because of the using of different datafiles (xlsx and csv). Where do I know, how the first table is called? Like for table 2 it's tbl_JD_PE.

[rwunderlich]

A best practice is to use an explicit table label in the script: 

MyTable:
LOAD *
FROM [lib://.........csv]
(txt, utf8, embedded labels, delimiter is '\t', msq);

This will create a table named "MyTable". 

If you do not provide a label statement, the assigned table name will depend on the file type. For txt files, the default is the filename.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com