topic Re: Qlik Sense - Mapping two Tables together in Connectivity & Data Prep

Qlik Sense - Mapping two Tables together

Thu, 16 Feb 2017 00:11:43 GMT

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

Re: Qlik Sense - Mapping two Tables together

shraddha_g — Fri, 17 Feb 2017 07:31:45 GMT

You can connect those tables by creating Composite key of Company Name and Project

Re: Qlik Sense - Mapping two Tables together

Anonymous — Fri, 17 Feb 2017 09:15:34 GMT

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

Re: Qlik Sense - Mapping two Tables together

ahaahaaha — Fri, 17 Feb 2017 09:22:42 GMT

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