Join two tables on multiple columns

[#1]

Hi All,

Can any one explain how to join two tables on multiple join conditions.

I have two table with 3 common fields. I need these fields in report. i don't want to concatenate these fields. If concatenate how get back these fields.

Thanks.

[sunny_talwar]

Do you want to keep them as separate tables and let QlikView handle the join implicitly? If yes, then check this: Concatenate vs Link Table

Else for join, you can keep the field names as is and join them: Understanding Join, Keep and Concatenate

[MindaugasBacius]

Try to load two tables with Noconcatenate statement.

Then you should get a synthetic key containing 3 common fields.

Best practice is to avoid synthetic key by using Autonumber statement that contains 3 common fields. Just load those 3 fields in one table not in both.

[Gysbert_Wassenaar]

Give the fields on which the tables should be joined the same name.

[jayanttibhe]

Hi Hari,

Which join you want to use? I guess it should be INNER JOIN. I would suggest create the Composite Key by Using Autonumber function for both the tables and then join the two tables on this new Key and then Drop the 3 fields from Either of the table as appropriate.

Thanks

[#6]

Thanks for reply

[muthukumar77]

Hi,

Use like this,

Table1:

Load

City,

State,

Country,

City & State & Country as Key,

Column1

from Table1;

Table2

Load

City & State & Country as Key,

Column2

from Table2;

I hope its helpful for you.

[oknotsen]

That is not a join plus you are doing exactly what the OP asked not to want: Concatenating those fields. What you are doing is just loading two tables.

[oknotsen]

To join two tables on multiple fields, just use JOIN. Yes, it is that easy. Qlik will recognize the fields with the same name and join the tables on those fields.

You can add LEFT, RIGHT, INNER or OUTER to the join to achieve the type of join you want.

[ramasaisaksoft]

Hi Hari,

My best idea is u need to create a Key field like

First_name &' '&Middle_Name&' '& Last_name as Full_name

need to create in both tables.

so that 2 tables will join on one KEY (Full_name ) itself without Synthetic Keys.