Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables that both contain "Company Name" and "Project", and then multiple non-matching columns.
I need the tables to act how a synthetic key would where the non-matching columns can communicate due to the Company Name and Project being the same.
All other tables in my data load have had no problem connecting through synthetic keys but there is this one instance where two tables are not communicating well so I want to map them together.
How do I do this in the data load editor?
Tom
You can connect those tables by creating Composite key of Company Name and Project
Hi, Tom,
Generally, synthetic key MUST be avoided.
If you only have few tables, Qlik maybe can still process it correctly, but if you have more tables, synthetic key not only make wrong mapping and does not work, it can also cause disaster by eating all your memory and even disk and crash your system, we had this bloody lesson before when adding several extra tabled to a exist app which have synthetic keys.
To avoid synthetic key, you need delete or join the tables with same key, and also using composite key to connect the tables as Shraddha pointed out, which can fix your current problem. But if you add more tables to your current app with synthetic key, you may get trouble gain in the future.
Link below is a blog talk about how to avoid synthetic key.
https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/
Zhihong
Hi Tom.
The idea of creating a synthetic key can be understood from this fragment of code with comments.
//create source tables Table01 and Table02
Table01:
LOAD
'Company'&Div(Rand()*10, 1) as Company,
'Poject'&Div(Rand()*10, 1) as Project,
'Dimension'&Div(Rand()*10, 1) as Dimension1,
Div(Rand()*10, 1) as Volume1
AutoGenerate 1000;
NoConcatenate
Table02:
LOAD
'Company'&Div(Rand()*10, 1) as Company,
'Poject'&Div(Rand()*10, 1) as Project,
'Dimension'&Div(Rand()*10, 1) as Dimension2,
Div(Rand()*10, 1) as Volume2
AutoGenerate 1000;
//form a synthetic key
NoConcatenate
Table1:
LOAD*,
Company & '|' & Project as %Key
Resident Table01;
NoConcatenate
Table2:
LOAD*,
Company & '|' & Project as %Key
Resident Table02;
DROP Tables Table01, Table02;
//create a connection table
ConnectTable:
Load Distinct
%Key,
%Key as %TimeKey,
Company,
Project
Resident Table1;
Concatenate (ConnectTable)
Load Distinct
%Key,
Company,
Project
Resident Table2
Where Not Exists(%TimeKey, %Key);
//delete unneeded fields
Drop Fields %TimeKey, Company, Project
From ConnectTable;
Drop Fields
Company, Project
From Table1;
Drop Fields
Company, Project
From Table2;
Result
Regards,
Andrey