topic Re: Basic Synthetic Key Question - QV in QlikView

Basic Synthetic Key Question - QV

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello Qlik Wizards!

Quick (and hopefully easy) Synthetic Key question. Here's my issue:

I have three tables [Applicant], [Applicant Child], and [Gender]. Applicant and Applicant Child are both normal entities while Gender is essentially a reference table. Both Applicant and Applicant Child have the field "Applicant ID" in them for relating to one another, but they also both have "Gender ID" so we're able to determine the Gender of both Applicants and their children. This creates a synthetic key with Applicant ID and Gender ID.

Is there a way to remove this Synthetic Key? Should I?

Thanks!

Re: Basic Synthetic Key Question - QV

muthukumar77 — Tue, 28 Feb 2017 05:24:52 GMT

Hi,

Can you share the data model or load script?

Re: Basic Synthetic Key Question - QV

vikasmahajan — Tue, 28 Feb 2017 05:26:49 GMT

While loading data Rename Gender Id as [Gender Id2]

Vikas

Re: Basic Synthetic Key Question - QV

vikasmahajan — Tue, 28 Feb 2017 05:28:56 GMT

See attached app

Vikas

Re: Basic Synthetic Key Question - QV

avinashelite — Tue, 28 Feb 2017 05:36:45 GMT

couple if ways ..

1.Composite key , create the composite key as below in both the tables

Applicant ID&'_'&Gender ID as Key

and rename the Applicant ID and Gender ID in both the table

2.Alias

rename Gender ID in both the table as Gender ID as Table1_Gender ID

and Gender ID as Table2_Gender ID

Re: Basic Synthetic Key Question - QV

rahulpawarb — Tue, 28 Feb 2017 06:11:01 GMT

Hello Weston,

Trust that you are doing well!

Please refer below given draft version of script:

//Mapping table for Gender details
[Map_Gender]:
Mapping LOAD * INLINE [
Gender ID, Desc
M, Male
F, Female
];


//Applicant table load with Applicant Gender details using ApplyMap
[Applicant]:
LOAD [Applicant ID],
     ApplyMap('Map_Gender', [Gender ID]) AS ApplicantGender,
     [Name],
     [Admission Date];
LOAD * INLINE [
Applicant ID,Gender ID,Name,Admission Date
1, F, Mary Matthew, 01/20/2017
2, M, Steve Smith, 02/05/2017
3, F, Albie Lee, 03/11/2017
];


//Applicant Child table load with ChildApplicant Gender details using ApplyMap
[Applicant Child]:
LOAD [Applicant ID],
     ApplyMap('Map_Gender', [Gender ID]) AS ChildApplicantGender,
     [Birth Date];
LOAD  * INLINE [
Applicant ID, Gender ID, Birth Date
1, M, 01/20/2017
3, M, 02/08/2017
3, F, 02/08/2017
];

Regards!

Rahul

Re: Basic Synthetic Key Question - QV

priyasawant — Tue, 28 Feb 2017 06:17:47 GMT

Can you share your dummy data

Re: Basic Synthetic Key Question - QV

tyagishaila — Tue, 28 Feb 2017 06:47:16 GMT

you can use any one method to remove Synthetic Key,

1. Either make composite keys to link [Applicant] & [Applicant Child] (May be this one is more suitable in this situation)

2. Or rename GenderID as App_GenderId & AppChild_GenderID

To get more accurate explanation, share dummy data.

Re: Basic Synthetic Key Question - QV

Anonymous — Tue, 28 Feb 2017 14:23:55 GMT

Here are the three main tables. Each is loading from an extracted table from an Oracle database.

//-----ApplicantChild Table-----

ApplicantChild:

Load

"ID" as [Applicant Child ID],

"APPLICANTID" as [Applicant ID],

"GENDERID" as [Gender ID]

Resident [OSUSR_H0L_APPLICA4];

//-----Applicant Table-----

Applicant:

Load

"ID" as [Applicant ID],

"GENDERID" as [Gender ID]

Resident [OSUSR_H0L_APPLICAN];

//-----Gender Table-----

Gender:

Load

"ID" as [Gender ID],

"CODEVALUE" as [Gender Code Value],

"DESCRIPTION" as [Gender Description],

"SORTORDER" as [Gender Sort Order],

"ACTIVEFLAG" as [Gender Active Flag]

Resident [OSUSR_VL2_GENDER];

Re: Basic Synthetic Key Question - QV

Anonymous — Tue, 28 Feb 2017 14:27:34 GMT

I'm using a "Gender" table that is a reference table for the other tables.

So the Applicant and ApplicantChild tables hold values "1" or "2" that are Foreign Keys to the Gender Table. In the Gender table, there is another field that holds the values "M" and "F"

Won't renaming the field to GenderID2 break the link in the model?

Re: Basic Synthetic Key Question - QV

Miguel_Angel_Baeyens — Tue, 28 Feb 2017 14:30:51 GMT

Synthetic keys are not that wrong if they happen on purpose and can be kept safely on the model without any significant impact on performance. There is no need for a composite key which basically requires a maintenance that otherwise QlikView does automatically.

However, if the fields are named alike but the values do not correspond (for example, Gender is Male/Female in one case and M/F in the other, and ID contains customer ID in one case and gender ID in another) the resulting synthetic table would be a cartesian product of all possible values, due to a poorly designed data model which is what it causes performance issues and problems with selections.

Abounding on the concept: Synthetic Keys

Re: Basic Synthetic Key Question - QV

Anonymous — Tue, 28 Feb 2017 14:31:23 GMT

See above for data model.

If I alias both GenderID fields, won't it break connection to the third table that holds the reference value? Or, how would I point the GenderID FK to the third table to get the corresponding "M" or "F"

Re: Basic Synthetic Key Question - QV

Anonymous — Tue, 28 Feb 2017 14:37:05 GMT

Thanks for the response.

Totally agree. I don't inherently have an issues with a Synthetic Key, but I'm not always sure when I should spend time trying to get rid of them.

I don't want to use a Composite key here because the Applicant ID the the PK across a dozen or more tables in the model.

Thanks for the thoughts.

Re: Basic Synthetic Key Question - QV

buzzy996 — Tue, 28 Feb 2017 14:39:42 GMT

i think u leave it the gender table this case.

do u think there is a advantage to create another table like gender in this model?

Re: Basic Synthetic Key Question - QV

avinashelite — Tue, 28 Feb 2017 14:52:18 GMT

try this script

//-----ApplicantChild Table-----

ApplicantChild:

Load

"APPLICANTID"&'_'&GENDERID as Key,

"ID" as [Applicant Child ID],

"APPLICANTID" as [Applicant ID ApplicantChild],

"GENDERID" as [Gender ID ApplicantChild]

Resident [OSUSR_H0L_APPLICA4];

//-----Applicant Table-----

Applicant:

Load

"ID"&'_'&GENDERID as Key,

"ID" as [Applicant ID ApplicantChild],

"GENDERID" as [Gender ID Applicant]

Resident [OSUSR_H0L_APPLICAN];

Master_Link:

LOAD Key,

[Gender ID ApplicantChild] as [Gender ID]

resident

ApplicantChild;

LOAD Key,

[Gender ID Applicant] as [Gender ID]

resident

Applicant;

//-----Gender Table-----

Gender:

Load

"ID" as [Gender ID],

"CODEVALUE" as [Gender Code Value],

"DESCRIPTION" as [Gender Description],

"SORTORDER" as [Gender Sort Order],

"ACTIVEFLAG" as [Gender Active Flag]

Resident [OSUSR_VL2_GENDER];

Basic Synthetic Key Question - QV

Re: Basic Synthetic Key Question - QV

adamdavi3s — Tue, 28 Feb 2017 14:53:21 GMT

A quick link table might just help clean up the data model, I would personally choose to do this because I like the model to be synthetic key free for my own sanity! Might be overkill though

//-----ApplicantChild Table-----

ApplicantChild:

Load rowno() as %ACKEY

"ID" as [Applicant Child ID],

"APPLICANTID" as [Child Applicant ID],

"GENDERID" as [Child Gender ID]

Resident [OSUSR_H0L_APPLICA4];

//-----Applicant Table-----

Applicant:

Load as %APKEY

"ID" as [Applicant Applicant ID],

"GENDERID" as [Applicant Gender ID]

Resident [OSUSR_H0L_APPLICAN];

//-----LinkTable-----

Link:

LOAD %ACKEY, [Child Applicant ID] as [Applicant ID], [Child Gender ID] as [Gender ID]

Resident ApplicantChild;

concatenate

LOAD %APKEY, [Applicant Applicant ID] as [Applicant ID], [Applicant Gender ID] as [Gender ID]

Resident Applicant;

DROP FIELDS [Child Applicant ID], [Applicant Applicant ID] , [Child Gender ID] , [Applicant Gender ID]

//-----Gender Table-----

Gender:

Load

"ID" as [Gender ID],

"CODEVALUE" as [Gender Code Value],

"DESCRIPTION" as [Gender Description],

"SORTORDER" as [Gender Sort Order],

"ACTIVEFLAG" as [Gender Active Flag]

Resident [OSUSR_VL2_GENDER];