topic Re: Synthetic key in QlikView

Synthetic key

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to remove this circular reference created with this synthetic key. Here i have connected with ODBC connector(Data Load editor). Can you help me with the changes required for removing the synthetic key.

Thanks.

LIB CONNECT TO 'Microsoft_SQL_Server_KWTPRAPCSQL01';

LOAD ClientNumber;

[Member]:

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

LOAD TransactionSlip,

StoreID,

BrandName;

[TransactionContent]:

SELECT TransactionSlip,

StoreID,

BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

LOAD TransactionSlip,

StoreID,

ClientNumber;

[TransactionFeed]:

SELECT TransactionSlip,

StoreID,

ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Re: Synthetic key

migueldelval — Tue, 27 Dec 2016 12:26:58 GMT

Hi Deepika,

Try it:

[Member]:

LOAD ClientNumber as Client_Key;

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

LOAD TransactionSlip,

StoreID,

BrandName;

SELECT TransactionSlip,

StoreID,

BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

[TransactionFeed]:

LOAD TransactionSlip,

StoreID,

ClientNumber as Client_Key;

SELECT TransactionSlip,

StoreID,

ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Regards

Miguel del Valle

Re: Synthetic key

sunny_talwar — Tue, 27 Dec 2016 12:28:38 GMT

Just try this (I don't think you need a preceding load here)

LIB CONNECT TO 'Microsoft_SQL_Server_KWTPRAPCSQL01';

[Member]:

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

SELECT TransactionSlip,

StoreID,

BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

[TransactionFeed]:

SELECT TransactionSlip,

StoreID,

ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Or you might be able to create a link table or join TransactionContent and TransactionFeed in order to avoid synthetic key, but synthetic keys are not always bad so unless there is a one to one relation or one to many, I wouldn't join them.

Re: Synthetic key

Wed, 28 Dec 2016 05:23:30 GMT

Hi Miguel,

Than k you for your response. The transaction slip and storeID still shows as synthetic keys.

Deepika

Re: Synthetic key

sankartalluru — Wed, 28 Dec 2016 06:41:48 GMT

Hi,

We need to design the modeling based on requirement.

as per my understanding below will work

[Member]:

SELECT

ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionFeed]:

SELECT

TransactionSlip,

StoreID,

ClientNumber,

StoreID&''&TransactionSlip as KEY,

FROM "APC_PROD_APC".dbo.TransactionFeed;

[TransactionContent]:

SELECT

StoreID&''&TransactionSlip as KEY,

StoreID as StoreID_TC,

TransactionSlip as TransactionSlip_TC

BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

Re: Synthetic key

migueldelval — Thu, 29 Dec 2016 12:18:30 GMT

Hi Deepika:

Try it:

[Member]:

LOAD ClientNumber as Client_Key;

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

LOAD TransactionSlip,

StoreID,

BrandName;

SELECT TransactionSlip as TransactionSlipContent,

StoreID,

BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

[TransactionFeed]:

LOAD TransactionSlip,

StoreID,

ClientNumber as Client_Key;

SELECT TransactionSlip,

StoreID,

ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Regards

Miguel del Valle