Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP

Script - Synthetic Key

How to remove Synthetic Key enclosed file?

5 Replies
MayilVahanan

HI

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MK_QSL
MVP
MVP
Author

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.

CELAMBARASAN
Partner - Champion
Partner - Champion

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

MK_QSL
MVP
MVP
Author

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 .........

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

www.masterssummit.com

www.naturalsynergies.com