topic Re: Join two tables on multiple columns in QlikView

Join two tables on multiple columns

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Join two tables on multiple columns

sunny_talwar — Wed, 18 May 2016 18:16:47 GMT

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

Re: Join two tables on multiple columns

MindaugasBacius — Wed, 18 May 2016 18:18:37 GMT

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.

Re: Join two tables on multiple columns

Gysbert_Wassenaar — Wed, 18 May 2016 18:21:03 GMT

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

Re: Join two tables on multiple columns

jayanttibhe — Sat, 21 May 2016 00:45:44 GMT

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

Re: Join two tables on multiple columns

Sun, 22 May 2016 06:47:00 GMT

Thanks for reply

Re: Join two tables on multiple columns

muthukumar77 — Sun, 22 May 2016 12:07:54 GMT

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.

Re: Join two tables on multiple columns

oknotsen — Sun, 22 May 2016 21:28:35 GMT

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.

Re: Join two tables on multiple columns

oknotsen — Sun, 22 May 2016 21:30:52 GMT

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.

Re: Join two tables on multiple columns

ramasaisaksoft — Mon, 23 May 2016 11:40:55 GMT

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.

Re: Join two tables on multiple columns

oeobiora — Wed, 19 Jun 2019 22:27:40 GMT

Could you help me re-create the equivalent of this statement in a load script?

It's from an access query.

SELECT

part_characteristics.ATA_location,

part_characteristics.engine_configuration_seq,

part_characteristics.part_nomenclature,

part_ata_lookup.part_number,

engines.engine_configuration_seq,

engines.engine_serial

events.event_seq,

events.event_date

events.vdata_db_id

FROM ((part_characteristics INNER JOIN part_ata_lookup ON (part_characteristics.ATA_location = part_ata_lookup.ATA_location) AND (part_characteristics.engine_configuration_seq = part_ata_lookup.engine_config_seq)) INNER JOIN (debit_doc_line_items INNER JOIN debit_docs ON debit_doc_line_items.debit_doc_seq = debit_docs.debit_doc_seq) ON (part_ata_lookup.ATA_location = debit_doc_line_items.ATA_location_code) AND (part_ata_lookup.engine_config_seq = debit_doc_line_items.engine_configuration_seq)) INNER JOIN (((vendor_job_number INNER JOIN debit_doc_applications ON vendor_job_number.vendor_job_number_seq = debit_doc_applications.vendor_job_number_seq)INNER JOIN events ON vendor_job_number.event_seq = events.event_seq) INNER JOIN engines ON events.engine_seq = engines.engine_seq) ON debit_docs.debit_doc_seq = debit_doc_applications.debit_doc_seq;