Basic Synthetic Key Question - QV

[Anonymous]

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!

[muthukumar77]

Hi,

Can you share the data model or load script?

[vikasmahajan]

While loading data Rename Gender Id as [Gender Id2]

Vikas

[vikasmahajan]

See attached app

Vikas

[avinashelite]

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

[rahulpawarb]

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

[priyasawant]

Can you share your dummy data

[tyagishaila]

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.

[Anonymous]

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];

[Anonymous]

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?