Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vanand3535
Contributor

joining 2 tables with two key in Qlik Sense Data load editor

Hi all,

I have to join two table with one columns how can I do this?

Table1--------country(filed)

Table2--------city(filed)

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

you're missing just the rename (it is the only way Qlik recognize a joining field)

try like this:

Table1:

LOAD

.

.

country as KEY


FROM...


LEFT JOIN(Table1)

LOAD

.

.

city as KEY


FROM...

8 Replies
vanand3535
Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

Normally we can write before LOAD expression of table2 as:-

Left join(Table1)

it will work but I have to join Table1 with country field and table2 with city filed.

Re: joining 2 tables with two key in Qlik Sense Data load editor

what is your expected output?

explain with sample data.

You can try with Concatenate instead of join

Regards,

Regards,
Prashant Sangle
vanand3535
Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

Hi Prashanth,

I have two tables Injury and Participation which has around 30 columns in each table. Now, i want to join Injury with Participation on only one Column (Join on ID). How do i do this? I tried using Concatenate option but since i have the same column names in both the tables for some columns it is joining on those as well but i need the join to happen only on one Column i.e ID.

Can you please help me out

Thanks,

Vivek

YoussefBelloum
Esteemed Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

you're missing just the rename (it is the only way Qlik recognize a joining field)

try like this:

Table1:

LOAD

.

.

country as KEY


FROM...


LEFT JOIN(Table1)

LOAD

.

.

city as KEY


FROM...

joseph_eftamand
Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

If you want to Join the two tables then concatenate is not the option you should use. Concatenate is like Union in sql. Sounds like you should do a left join:

Participation:

Load

Field1 AS %Join_ID,

Field2,

Field3

FROM XXXX;

left join(Participation)

FieldX AS %Join_ID,

FieldF

FROM XXXX;

QlikView will perform the join on the common fields. You need to make sure you name the ID field the same in the both tables.

Check this out for more on joins and concatenations:

Understanding Join, Keep and Concatenate

OmarBenSalem
Esteemed Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

If you have same column names in both your tables, Qlik will automatically associate ur 2 tables using these fields in common.

In fact, for Qlik, the key that associates 2 tables is the FIELD that shares the same NAME btwn 2 tables.

So, what you should do is RENAME these fields so that they won't be the same anymore and NAME your ID field in both tables the same way.

sthing like this :

Table1:

load field1,field2,field3,

YourID1 as KEY

from source1;

JOIN (Table1)

load fieldA,field2 as fieldB,fieldC,

YourID2 as KEY

from source2;

Re: joining 2 tables with two key in Qlik Sense Data load editor

sorry but I am still confused with your explanation. Can you explain with sample data, so that we can provide exact solution .

Regards,

Regards,
Prashant Sangle
joseph_eftamand
Contributor

Re: joining 2 tables with two key in Qlik Sense Data load editor

Hi Prashant,

Have you checked this link:

Understanding Join, Keep and Concatenate? It offers a detailed explanation.

Have a look here as well:

https://www.qlikviewaddict.com/2012/03/explaining-joins.html?m=1

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Join.htm

A sample app won't really provide any more information than that I am afraid.

Hope it helps.

Community Browser