topic Re: What makes a "Perfect Key"? in QlikView

What makes a "Perfect Key"?

mike_garcia — Thu, 08 Apr 2010 17:36:14 GMT

Hello,

Going through one of the Demos, I stumbled upon a concept that I'm curious to know about: 'Perfect Keys'.

So far, I have seen three types of keys: Primary Key, Perfect Key and Key. What are the differences among those? Is there any other type? Does a Perfect Key make a model more efficient?

This is a screenshot showing the 'Perfect Key' Legend:

and this one is showing only 'Key':

Thanks in advance for sharing your knowledge about this.

Mike.

What makes a "Perfect Key"?

johnw — Thu, 08 Apr 2010 21:29:38 GMT

While I don't actually know the answers, with a brief glance at some sample tables, it looks like they're defined as follows:

Primary Perfect
Key Key Key
Y Y Y Links two or more tables together?
Y Y Corresponds to one and only one row of the table?
Y Every value of key field appears in the table?

Re: What makes a "Perfect Key"?

Tue, 04 Nov 2014 04:02:17 GMT

From the Book QlikView 11 for Developers:

"The name of the field. Optionally, if the field is a key field, a qualifier is shown enclosed in square brackets. This qualifier indicates the following levels of key quality:

[Perfect Key] indicates that every row contains a key value, and that all of these key values are unique. At the same time, the field's subset ratio is 100 percent. This qualifier should be seen in dimension tables, where every key should uniquely identify a single record.

[Primary Key] indicates that all key values are unique, but not every row contains a key value or the field's subset ratio is less than 100 percent

[Key] indicates that the key is not unique. This qualifier is usually seen in fact tables, where the same dimension value may be associated with many different facts."

Re: What makes a "Perfect Key"?

Anonymous — Tue, 04 Nov 2014 05:32:40 GMT

Perfect Key Vs Primary key:

The [Perfect Key] is a bit more: it is a [Primary Key] (unique) with no "orphans".

Let me clarify this with 2 examples:

Example 1)

InvoiceDetails:

LOAD *

INLINE [

InvoiceID, InvoiceLine, ProductID, Quantity, Amount

Inv001,1,PR01,10,50

Inv001,2,PR02,10,40

Inv002,1,PR01,30,150

Inv002,2,PR03,10,800

];

Invoices:

LOAD *

INLINE [

InvoiceID, ClientID

Inv001,CL0001

Inv002,CL0002

];

In this example, InvoiceID is [Key] in InvoiceDetails and [Perfect Key] in Invoices: it is unique and it has no orphans.

Example 2)

InvoiceDetails:

LOAD *

INLINE [

InvoiceID, InvoiceLine, ProductID, Quantity, Amount

Inv001,1,PR01,10,50

Inv001,2,PR02,10,40

Inv002,1,PR01,30,150

Inv002,2,PR03,10,800

Inv003,1,PR01,10,50

];

Invoices:

LOAD *

INLINE [

InvoiceID, ClientID

Inv001,CL0001

Inv002,CL0002

];

In this example, InvoiceID is [Key] in InvoiceDetails and [Primary Key] in Invoices. There is a value in InvoiceDetails, Inv003, that does not find a corresponding value in the Invoices table. This is called a "orphan". For this reason InvoiceID is not anymore perfect, but it is still primary, because it is unique.

If you copy-paste the example you can verify it

BR,

Chinna

Re: What makes a "Perfect Key"?

alexdataiq — Mon, 30 May 2016 23:37:01 GMT

This is kind of funny, because Miguel [original poster] is the author of that book!

Cheers