Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vanand3535
Creator III
Creator III

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
Champion
Champion

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...

View solution in original post

9 Replies
vanand3535
Creator III
Creator III
Author

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.

PrashantSangle

what is your expected output?

explain with sample data.

You can try with Concatenate instead of join

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
vanand3535
Creator III
Creator III
Author

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
Champion
Champion

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...

View solution in original post

joseph_eftamand
Partner
Partner

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
Partner
Partner

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;

PrashantSangle

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
joseph_eftamand
Partner
Partner

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.

kar301281
Contributor
Contributor

Hi 

 

I have multiple dimension table where each dimension tables has 4 keys and the fact table also has the same and it is creating as many synthetic keys for each. 

 

For ex: 

 

i have 3 tables with same columns    so i want to create a data model  as star schema     how can i do this?     i dont want single table data model .....  i want star schema model.

 

TableA ( DIMENSION1)

Id1

id2

id3

id5

E

F

 

TableB ( DIMENSION2)

id1

id2

id3

id4

G

H

FACT(F1)

id1

id2

id3

id4

Revenue

sales 

 

FACT(F2)

id1

id2

id3

id4 

Loss_revenue

Loss_sales

 

Etc. 

 

How we can do the datamodeleing eliminating synthetic keys for the same. give some examples where if any one handled multiples keys between multiple dimension and multiple facts and removing synthetic keys. 

 

 

 

 

Thanks