synthetic Key related problem

[Anonymous]

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

[preminqlik]

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

[MK_QSL]

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

[Anonymous]

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

[#5]

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

As key is subset of Key1.

[Anonymous]

Hi Neha,

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

Thanks.

[#7]

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.

[Anonymous]

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

[#9]

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.

[Anonymous]

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