topic Re: Synthetic Key: Two almost identical tables with same field name in App Development

Synthetic Key: Two almost identical tables with same field name

PamelaAlcantara — Wed, 24 Jun 2020 20:50:33 GMT

Hi,

I am trying to load 3 tables from Teradata. Two of those tables are huge and contain the same name in almost every field (around 60 fields each). When I try to load the data, it takes more than one hour and then I get the error that same something went wrong.

I believe it is because Qlik Sense is creating synthetic keys for each field that share the same name and as I mentioned there are many.

Is there a way to change the name of each field before loading the data? So Qlik doesn't create all those synthetic keys?

I already tried the following, but I keep getting an error

LOAD
[Item_ID] AS [R_Item_ID],
[Case_ID] AS [R_Case_ID],

etc,

Does anyone know how to avoid the creation of synthetic keys by renaming name fields?

Thank you in advance!

Pamela

Re: Synthetic Key: Two almost identical tables with same field name

Saravanan_Desingh — Wed, 24 Jun 2020 20:55:55 GMT

Try with

QUALIFY *;

Re: Synthetic Key: Two almost identical tables with same field name

Tanalex — Wed, 24 Jun 2020 20:56:06 GMT

QUALIFY

https://help.qlik.com/en-US/sense/April2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Qualify.htm

It prepends the table name to the column name. You can then UNQUALIFY the key field(s), so it does not get the table name prepended to it and can be the join column.

Re: Synthetic Key: Two almost identical tables with same field name

Vegar — Wed, 24 Jun 2020 22:31:13 GMT

As earlier replies sai , QUALIFY, is a way to go.

An alternative solution to consider is to concatenate the two data sets into one larger transaction table.

Re: Synthetic Key: Two almost identical tables with same field name

Beneliasq — Thu, 25 Jun 2020 00:49:19 GMT

Agree with the replies above, and you could try something like this.

Qualify *;

UnQualify [Key fields]; //if there are any key fields on which the association should happen

Table1:

Load statement;

Table2:

Load statement;

UnQualify *;

Re: Synthetic Key: Two almost identical tables with same field name

marksouzacosta — Thu, 23 Jul 2020 04:18:33 GMT

Hi Pamela,

We just released a new video in our YouTube channel related to your question.
I hope it helps!

IPC Tips - Removing Synthetic Keys
https://youtu.be/tETUxntiqew

Regards,

Mark Costa