topic Re: synthetic Key related problem in QlikView

synthetic Key related problem

Anonymous — Mon, 03 Mar 2014 08:05:41 GMT

Hi Everyone,

Here as below in both table we have two key { key and Key1} on the basis of two key we want to link both table and should not create the synthetic key . Means both table should be connect with key and Key1.

Is it possible?

Please anyone help me.

Thanks

Master:

LOAD USER_ID & DealerCode as key,

USER_ID&DealerCode&DealerType as Key1,

USER_ID,

USERNAME,

DealerCode,

DealerName,

State,

City,

Region,

DealerType

FROM

MasterTable.xlsx

(ooxml, embedded labels, table is master);

//**********************Transaction Table*********************

Transaction:

Left Keep LOAD USER_ID&DealerCode as key,

USER_ID&DealerCode&DealerType as Key1,

USER_ID,

DealerCode,

DealerType,

Date(DATE,'DD-MM-YYYY') AS DATE,

Amount,

Quantity,

Tax

FROM

Tab1.qvd

(qvd);

Re: synthetic Key related problem

preminqlik — Mon, 03 Mar 2014 08:13:01 GMT

use only key1 ...it will automatically act as " key " too...can you post your requirement ?

Re: synthetic Key related problem

MK_QSL — Mon, 03 Mar 2014 08:13:03 GMT

There are many ways of avoiding synthetic Key..

i.e.

renaming the keys (this may not work here as you definitely want to keep same field name)

CONCATENATE two tables

Create a LINK Table

Re: synthetic Key related problem

Anonymous — Mon, 03 Mar 2014 08:28:28 GMT

Hi Prem,

Thanks for the reply here is my actual requirement.

We have 4th table in which one is mater and three transactions table .

In master table we have the following field

userid, dealercode, state , region, city, UserName, DealerName & dealerType.

In First transaction table field

userid, dealercode, date and amount

Second transaction table field

userid, dealercode, date and quantity

Third transaction table field

userid, dealercode, date, dealerType and Tax

The condition is 1st and 2nd transaction table should be connect with master table on the basis of userid, dealercode,

Third transaction table should be connect with master table on the basis of userid, dealercode & dealerType

As of client requirement in the data model should be only one master and one transaction table.

avoid the use of join and applymap fuction .

Thanks

Re: synthetic Key related problem

Mon, 03 Mar 2014 08:44:06 GMT

Best way is to use only Key1 and remove other key.

As key is subset of Key1.

Re: synthetic Key related problem

Anonymous — Mon, 03 Mar 2014 09:32:57 GMT

Hi Neha,

Thanks for the reply. But the condition is that both key should be use in the linking of both table..

Thanks.

Re: synthetic Key related problem

Mon, 03 Mar 2014 09:59:46 GMT

According to your requirement,

It is neither required to use both the keys in transaction table nor left keep or join.

Use following code:

Master:

LOAD USER_ID & DealerCode as key,

USER_ID&DealerCode&DealerType as Key1,

USER_ID,

USERNAME,

DealerCode,

DealerName,

State,

City,

Region,

DealerType

FROM

MasterTable.xlsx

(ooxml, embedded labels, table is master);

//**********************Transaction Table*********************

Transaction1:

LOAD USER_ID&DealerCode as key,

Date(DATE,'DD-MM-YYYY') AS DATE,

Amount

FROM

Tab1.qvd

(qvd);

Transaction2:

LOAD USER_ID&DealerCode as key,

Date(DATE,'DD-MM-YYYY') AS DATE,

Quantity

FROM

Tab2.qvd

(qvd);

Transaction3:

LOAD USER_ID&DealerCode&DealerType as Key1,

Date(DATE,'DD-MM-YYYY') AS DATE,

Tax

FROM

Tab3.qvd

(qvd);

Instead of loading Date field thrice check for appropriate transaction table and load it once. Otherwise date field will create SYNTHETIC KEY.

Re: synthetic Key related problem

Anonymous — Mon, 03 Mar 2014 10:10:19 GMT

Hi Neha,

Thanks for the reply . This is the way bt the client requirement is that in the data model should be only one master and one transaction table , and both keys should be there..

Thanks

Re: synthetic Key related problem

Mon, 03 Mar 2014 10:18:51 GMT

You will have to explain this to your client that both the situations are not possible. They will have to pick only one.

Anyways, it is little weird, your client is interested in data model instead of correct data and accurate results.

Re: synthetic Key related problem

Anonymous — Mon, 03 Mar 2014 10:36:00 GMT

Hi Neha,

I have suggested to the client that is not possible still just i was try that really it is possible or not bcz I'm new in qlikview .

Thanks

Re: synthetic Key related problem

preminqlik — Wed, 05 Mar 2014 04:24:12 GMT

yes i agree with neha ...according to my understanding , your master has one to one data , so for example for every dealer type also there is only one dealer must be there ...so understand the concept...you just conncet thorugh "Key1"(as you did already ) thats it.....

Re: synthetic Key related problem

arsal_90 — Wed, 16 Apr 2014 07:14:10 GMT

Please use this to overcome with your problem i make a link table

Transaction1:

Load

userid & ‘_’ & dealercode & ‘_’ & date as Key1,

amount

FROM Transact1;

Transaction2:

Load

userid & ‘_’ & dealercode & ‘_’ & date as Key2,

quantity

FROM Transact2;

Transaction3:

Load

userid & ‘_’ & dealercode & ‘_’ & date as Key3,

dealerType,

Tax

FROM Transact3;

LinkTable:

Load DISTINCT

userid & ‘_’ & dealercode & ‘_’ & date as Key1,

userid & ‘_’ & dealercode & ‘_’ & date as Key2,

userid & ‘_’ & dealercode & ‘_’ & date as Key3,

userid,

dealercode,

date

FROM Transact1;

LinkTable:

Load DISTINCT

userid & ‘_’ & dealercode & ‘_’ & date as Key1,

userid & ‘_’ & dealercode & ‘_’ & date as Key2,

userid & ‘_’ & dealercode & ‘_’ & date as Key3,

userid,

dealercode,

date

FROM Transact2;

LinkTable:

Load DISTINCT

userid & ‘_’ & dealercode & ‘_’ & date as Key1,

userid & ‘_’ & dealercode & ‘_’ & date as Key2,

userid & ‘_’ & dealercode & ‘_’ & date as Key3,

userid,

dealercode,

date

FROM Transact3;

Re: synthetic Key related problem

arsal_90 — Wed, 16 Apr 2014 07:27:39 GMT

Concatenation Method

Transaction1:

Load

Userid &’-‘& Dealercode as Key,

//Userid,

// Dealercode,

Date,

Null() as Tax,

Null() as quantity,

amount

FROM Transact1;

Transaction2:

Load

Userid &’-‘& Dealercode as Key,

// Userid,

//Dealercode,

Date,

Null() as Tax,

Null() as amount,

quantity

FROM Transact2;

Transaction3:

Load

Userid &’-‘& Dealercode as Key,

//Userid,

// Dealercode,

Date,

Tax,

Null() as amount,

Null() as quantity

//dealerType

FROM Transact3;

Master:

Load

Userid &’-‘& Dealercode as Key,

userid,

dealercode,

state ,

region,

city,

UserName,

DealerName ,

dealerType From Master;

If still have problem then please share your qvw with dumy data and also output what you want