topic Script - Synthetic Key in QlikView

Script - Synthetic Key

MK_QSL — Fri, 23 Aug 2013 14:10:24 GMT

How to remove Synthetic Key enclosed file?

Re: Script - Synthetic Key

MayilVahanan — Fri, 23 Aug 2013 14:16:55 GMT

Rename %PG as SomeOtherName in Inventory and SalesData table.

One More field as %PG in SalesData, for mapping purpose between SalesData and Purchase.

So, your SalesData looks like

Load

%PG as PG,

%PG,

From SalesData;

Inventory:

Load

%PG as PG,

From Inventory;

Hope it helps

Re: Script - Synthetic Key

MK_QSL — Fri, 23 Aug 2013 14:27:42 GMT

Thanks for your reply.

In Inventory and Sales Data %PG and %PrimeSupplierID are creating a Synthetic key.

%PG is coming from PRODUCTGROUP table and I need %PG in both above table.

Re: Script - Synthetic Key

CELAMBARASAN — Fri, 23 Aug 2013 14:41:14 GMT

I assumend Inventory has the master data.

Inventory:

LOAD

[%PG]

,[%PrimeSupplierID]

,[%PG] & [%PrimeSupplierID] AS [%PG_PrimeSupplierID]

,[ECP Value]

,.......

FROM Source;

SalesData:

LOAD

[%PG] & [%PrimeSupplierID] AS [%PG_PrimeSupplierID]

,[Sales Value]

,[Cost Value]

........

FROM Source;

It will form joins between two tables

Hope it helps

Celambarasan

Re: Script - Synthetic Key

MK_QSL — Fri, 23 Aug 2013 15:29:38 GMT

All three tables enclosed.... only necessary dimensions are shown....

Inventory:

LOAD [Prime Supplier] as %PrimeSupplierID,

[Part Number],

Left([Part Number],3) as %PG,

TOTAL as InventoryTotalValue,

SumOfQuantity as InventoryTotalQty

FROM .....

SalesData:

LOAD P_group2 as %PG,

PSupp as %PrimeSupplierID,

[Net Sales] as [Sales Value],

Cost as [Cost Value],

Qty as [Qty Sold],

Month as SalesMonth

FROM .....

PRODUCTGROUP:

LOAD PC as %PG,

PCNAME as PGName,

FROM .........

Re: Script - Synthetic Key

Oleg_Troyansky — Fri, 23 Aug 2013 15:51:29 GMT

This is a classic example of the typical Data Modelling challenge.You have 2 facts (Sales and Inventory) that share more than one common key. There are 3 approaches to this situation:

1. Do nothing and leave the Synthetic key (this "solution" was suggested by John Witherspoon, and a representative from QlikTech confirmed that this type of a synthetic key will work just fine. Many QlikView practitioners (myself included) prefer not to leave synthetic keys, for better scalability and for the sake of "clean programming".

2. Create a Link Table - identify your common keys, and create a single key, built as a concatenation of the individual keys. Your Facts will be linked by the combo key, and your common keys will only be stored in the Link Table. You can probably find more information about Link Tables if you search this forum.

3. Create a single Concatenated Fact. Concatenate both of your Fact tables into a single fact. All the common keys will get combined into a single field, and other fields will be added accordingly. This is the most scalable and the best performing model, however you lose the direct association between the two facts - in other words, it will be less trivial to show Sales Data for a Part Number associated with certain Inventory Balance, or to show Inventory of all the Items sold to a specific Customer (if that's needed). For those requirements, you need to create additional associations, but that's definitely too much to explain in a single forum message...

Barry Hamsen has an excellent 4-hour lecture on Data Modelling where he describes those models in great detail, as part of the Masters Summit for QlikView.

best,

Oleg Troyansky