Solved: Re: Getting synthetic keys error when load data - Qlik Community

RitaM · ‎2024-05-15

Hi

I am new in Qlik Sense

Trying to join 2 tables based on 2 columns in Data Load Editor

getting synthetic keys error when load data:

table1. header, ID, *

table2. header, ID, *

need to create join table1.header=table2.header and table1.ID=table2.ID

I am using Oracle database

Please advise.

Thanks

Lech_Miszkiewicz · ‎2024-05-15

Hi @RitaM ,

Lets be clear about using key words used in your question. When you say "Join 2 tables" based on your description I guess you actually dont mean "Join" but instead load them separately and create association between them based on 2 fields - right?

You probably need to create compound key (concatenate 2 fields into 1) but to answer that question Rob @rwunderlich has written excellent presentation about ways of dealing with synthetic keys so have a look at it:

https://qlikviewcookbook.com/resolving-synthetic-keys/

We dont know your data and Rob in his presentation ask multiple questions which you need to answer before you make a decision on how to solve it eventually based on your scenario.

Lastly - if you really wanted to perform "Join" and from 2 tables create one then you have 2 options:

during load from SQL you can perform SQL join which will run Oracle side
or load 2 tables from SQL and then use LEFT JOIN () / RIGHTJOIN () / INNER JOIN () or just JOIN () load prefix to perform it: https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

Lech_Miszkiewicz · ‎2024-05-15

Hi @RitaM ,

Lets be clear about using key words used in your question. When you say "Join 2 tables" based on your description I guess you actually dont mean "Join" but instead load them separately and create association between them based on 2 fields - right?

You probably need to create compound key (concatenate 2 fields into 1) but to answer that question Rob @rwunderlich has written excellent presentation about ways of dealing with synthetic keys so have a look at it:

https://qlikviewcookbook.com/resolving-synthetic-keys/

We dont know your data and Rob in his presentation ask multiple questions which you need to answer before you make a decision on how to solve it eventually based on your scenario.

Lastly - if you really wanted to perform "Join" and from 2 tables create one then you have 2 options:

during load from SQL you can perform SQL join which will run Oracle side
or load 2 tables from SQL and then use LEFT JOIN () / RIGHTJOIN () / INNER JOIN () or just JOIN () load prefix to perform it: https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

RitaM · ‎2024-05-16

Hi Lech_Miszkiewicz,

Thank you for your help!

I was able to create compound key1 and join 2 tables (table1 - Fact, table2 - dimensional)

Next I need to create another compound key2 and join 3-d table, to make sure that tabe1 joined to table2, table3, and etc. but I am getting error when try to join table3…

I need to archive Star schema model where table1 - fact and other tables are multiple dimension tables.

Any help appreciated.

Thanks,

Rita

Lech_Miszkiewicz · ‎2024-05-16

Hi,

what error are you getting @RitaM ?

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.