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

[vanand3535]

Hi all,

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

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

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

[vanand3535]

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,

[vanand3535]

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 · Accepted solution]

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]

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]

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,

[joseph_eftamand]

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]

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