topic Primary Key,Perfect Key,Key and information density, subset ratio help.. in QlikView

Primary Key,Perfect Key,Key and information density, subset ratio help..

Tue, 24 Dec 2013 17:19:17 GMT

Hi all,

Please find the my load script,

Invoices:

LOAD *

INLINE [

InvoiceID, ClientID

Inv001,CL0001

Inv002,CL0002

,CL0003 -> Here InvoiceID is Null

,CL0004

];

But, here showing Invoice is Perfect Key and Information density 100% and Subset ration 100%.

I have doubt, perfect key always Unique Id and Not null(Unique + Not Null) values right?

And,

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

,4,PR04,20,700

];

Here InvoiceID is 5th row is null, still showing Key and Information density 100% and Subset ration 100%.

i am bit confusion,

1.Perfect Key always Primary key,Unique Id and not null values Properly?

2.Subset ratio 100% means always having values Properly? .

if i understand wrong plz excuse.

Regards,

raja.

Re: Primary Key,Perfect Key,Key and information density, subset ratio help..

salto — Thu, 26 Dec 2013 14:00:01 GMT

Perfect key means that every row contains a key value, and all those values are unique. So you are right, perfect Key is Primary Key, unique and not null.

Primary Key: all values are unique, but not every row contains a key value (there can be null values)

Subset ratio: shows the percentage of all distinct values for a field in the table, compared to all the distinct values for that field in the entire data model.

Hope this helps.

Re: Primary Key,Perfect Key,Key and information density, subset ratio help..

Thu, 26 Dec 2013 15:13:37 GMT

Raja,

Take care of the LOAD INLINE that does not handle the Null() as you may expect. If you want to do tests, use Excel files that will correctly handle the null values and be easy to modify.

I would just add one thing to SALTO's comment:

To be perfect, a key must have a density and a subset ratio, both of 100% (it means that all the keys are not null, unique and in the table): in other words, if one key is outside the table, the key will not be perfect.

Fabrice