topic Re: Mapping load - Synthetic keys in QlikView

Mapping load - Synthetic keys

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

Hi Community,

What was the wrong in the below script as it creating synthetic keys

TRN_TM_RISK_COUNTRY_MAP:
Mapping LOAD
COUNTRY_CODE as T_M_COUNTRY_CODE,
COUNTRY_NAME as T_M_COUNTRY_NAME
From
EXT_COUNTRY.qvd](qvd);

T_TRANSITION_MATRIX:
LOAD *,
ApplyMap('TRN_TM_RISK_COUNTRY_MAP',T_M_COUNTRY_CODE) AS T_M_COUNTRY_NAME;
LOAD %T_M_ID ,
RISK_COUNTRY AS T_M_COUNTRY_CODE

FROM
TRANSITION_MATRIX_*.qvd (qvd) ;

Re: Mapping load - Synthetic keys

sunny_talwar — Fri, 04 Mar 2016 11:31:24 GMT

Just the above code itself is creating a synthetic key?

Re: Mapping load - Synthetic keys

maxgro — Fri, 04 Mar 2016 11:34:06 GMT

I think you should add the script or a Qlik doc.

It's difficult to understand where your small piece of code can create a syn key

And you can also read this

Synthetic Keys

Re: Mapping load - Synthetic keys

MayilVahanan — Fri, 04 Mar 2016 11:34:22 GMT

From your script, Result will have only one table .

Please provide full script

Re: Mapping load - Synthetic keys

avinashelite — Fri, 04 Mar 2016 11:38:03 GMT

Synthetic key will be formed when the tables are linked with more than one filed ...check the fields in your data model ...using table viewer will help you to identify the Synthetic keys

Re: Mapping load - Synthetic keys

priyarane — Fri, 04 Mar 2016 11:38:13 GMT

Hi Sunny,

we have synthetic keys like

Re: Mapping load - Synthetic keys

Anonymous — Fri, 04 Mar 2016 11:40:14 GMT

..or it could fail due to a closing square bracket with no corresponding opening square bracket.

Re: Mapping load - Synthetic keys

maxgro — Fri, 04 Mar 2016 11:41:00 GMT

maybe you can concatenate the 2 transition_matrix tables

Re: Mapping load - Synthetic keys

avinashelite — Fri, 04 Mar 2016 11:43:36 GMT

Check the

T_TRANSITION_MATRIX table and

CDM_T_TRANSITION_MATRIX_31_Mar-16

the above code is not causing the synthetic key

to avoid the synthetic key , you can rename the fields using as key word

if you want to like the tables with both the fields than build a composite key for the same

LOAD T_M_ID &'-'& T_M_COUNTRY_CODE as Key in both the tables and rename all the other fields

Re: Mapping load - Synthetic keys

MayilVahanan — Fri, 04 Mar 2016 11:43:48 GMT

Its depends on your data model,

We can remove synthetic key by followings

1. Rename the fields (For Ex: in CDM.Transition_matrix_31_Mar_16, you can rename T_M_Country_Code as CDM_T_M_Country_Code)

2. Create a composite key like %T_M_ID & T_M_Country_Code As %Key in Both Tables and "remove or rename" the common fields

3. You can go with concatenate the both tables