topic Re: How to Remove Syn Key - Data Model in QlikView

How to Remove Syn Key - Data Model

vikasmahajan — Mon, 21 Jul 2014 12:10:09 GMT

Dear all,

I have sales group master ( data indoco/spade/wareen) and head quarter master with sg id field when i link this with sales group master I am getting synthetic key

DIM_SalesGroupMaster:

LOAD SG_ID AS [Sales Group Code],

SG_NAME AS [Sales Group],

FROM

[..\Qvds\DIM_SalesGroupMaster.qvd]

(qvd);

DIM_GeoHQMaster:

HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')

LOAD Hqid AS [HQ Code],

Hqname AS [HQ],

GeoLevelName AS [Level Name],

CHILD_ID,

PARENT_ID,

Sgid as [Sales Group Code],

[EmpName],

[Email]

FROM

[..\Qvds\DIM_GeoHQMaster.qvd]

(qvd);

Please guide me how to remove syn key ?

Vikas

Re: How to Remove Syn Key - Data Model

Anonymous — Mon, 21 Jul 2014 12:19:03 GMT

I must admit that from you script I cannot see how it could have created a synthetic key. Do you have more script that creates other tables ?

Notwithstanding that I would suggest using a Mapping Load & Applymap as per the below, which will result in just one table. With just one table it is impossible to get a synthetic key generated.

SalesGroupMasterMAP:

mapping LOAD

SG_ID AS [Sales Group Code],

SG_NAME AS [Sales Group],

FROM

[..\Qvds\DIM_SalesGroupMaster.qvd]

(qvd);

DIM_GeoHQMaster:

HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')

LOAD Hqid AS [HQ Code],

Hqname AS [HQ],

GeoLevelName AS [Level Name],

CHILD_ID,

PARENT_ID,

Sgid as [Sales Group Code],

applymap ( 'SalesGroupMasterMAP', Sgid ) as [Sales Group],

[EmpName],

[Email]

FROM

[..\Qvds\DIM_GeoHQMaster.qvd]

(qvd);

Re: How to Remove Syn Key - Data Model

avinashelite — Mon, 21 Jul 2014 12:22:56 GMT

Hi Vikas,

If you want to combine tables more than one key Please use a link table it will avoid the formation of Synthetic keys.

Regards,

@vi

Re: How to Remove Syn Key - Data Model

vikasmahajan — Mon, 21 Jul 2014 12:28:17 GMT

Thanks for reply I have other tables also along with sales group field how solve with to generate link table ?

Vikas

Re: How to Remove Syn Key - Data Model

Anonymous — Mon, 21 Jul 2014 12:36:21 GMT

I have just noticed the you attached an image of some of your data model to your original post, and you sure do have a lot of tables.

Rather than worry about a synthetic key, I'd suggest you need to massively flatten your data model. In your script use joins / concatenations and try and get down to just one Fact Table.

the repeated use of ApplyMap() to include your dimensions into the flattened Fact Table.

I reckon that once you have sorted your data model then the synthetic key will have been sorted as per of that.