topic Removing synthetic keys in QlikView

Removing synthetic keys

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I am working in a project which there are several tables and most of them have same fields, and the result in their relation are lot of synthetic keys:

Generally, i could concatenate the tables to fix the synthetic keys, but there are distinct fields where i can´t concatenate.

Anyonve could help me how to solve the synthetic keys in this situation?

Regards,

Re: Removing synthetic keys

terezagr — Tue, 08 Mar 2016 18:25:28 GMT

What about using Qualify? And UnQualify those fields you want to link on.

How to Rename Fields

Re: Removing synthetic keys

Tue, 08 Mar 2016 19:08:42 GMT

Thanks Tereza,

I will try that.

Re: Removing synthetic keys

marcus_sommer — Tue, 08 Mar 2016 19:20:08 GMT

See here Synthetic Keys and more here Get started with developing qlik datamodels.

- Marcus

Re: Removing synthetic keys

vikasmahajan — Wed, 09 Mar 2016 06:44:41 GMT

Hi,

1) Refer this https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys and Best pratices PDF's which i have collected in the past.

another idea

2) My idea would be to use the Qualify statement.

Then all fields get the name of the table added which makes them unique

before the upload Qualify *;

Example

Qualify *;

Table1

Sales (becames Table1.Sales)

Location (becames Table1.Location)

Table2

Sales (becames Table2.Sales)

Location (becames Table2.Location)

Unqualify *;

3) You can use concatenate tables , joins

also good document from FEBRIC https://community.qlik.com/docs/DOC-5698

HTH

Vikas

Re: Removing synthetic keys

vikasmahajan — Wed, 09 Mar 2016 06:52:34 GMT

Also go through this link http://mindmajix.com/Synthetic-keys-in-Qlikview

Vikas

Re: Removing synthetic keys

Thu, 10 Mar 2016 15:12:42 GMT

Thanks for your tip Vikas!!

QUALIFY is good and easy solution for synthetic keys, however i lost the conexion between the tables.

Basically, Year and Month are the synthetic fields in my data model, and when i apply a filter for month or year, i would like that filter to be applied in all objects.

Is that the case to use Autonumber?

Regards,

Re: Removing synthetic keys

alexdataiq — Thu, 10 Mar 2016 15:50:03 GMT

Maybe you could try Unqualifying the Month and Year fields.

QUALIFY *;

UNQUALIFY Month, Year;

Or even better make a Master Calendar.

Regards,

Re: Removing synthetic keys

vikasmahajan — Fri, 11 Mar 2016 06:03:12 GMT

For Month, Year better to create a master calendar and link all facts

Thanks

Vikas