topic Re: Situation when I get A primary Key, instead of Key or Perfect Key. in QlikView

Situation when I get A primary Key, instead of Key or Perfect Key.

retko1985 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I was trying to learn about these concepts in Qlikview, and found some posts on forum, than tried to recreate it myself, and I can make a key to be Primary Key and show it when I hover over in Table Viewer.

Please see the attached QVW.

I made a table, where I am missing one key field, it is NULL (with some resident load conversion from empty string to null).

In table viewer it shows for the field InvoiceID from Invoices Table as KEY

Some sources say that KEY is not unique and appear in multiple rows (which I don't have in my example data). I think it should be Primary Key.

Can someone explain, why is it KEY and not a Primary Key?

Thank you.

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

swuehl — Sun, 19 Nov 2017 21:37:15 GMT

I think it follows this definition:

A table primary key’s main features are:

It must contain a unique value for each row of data.
It cannot contain null values.

A table's key field is in addition a perfect key if the table field values contain all symbol table values of the key field.

Note that the perfect / primary key label is specific to the table when hovering over the column in table view.

edit: not sure if this definition has changed over time with regard to how Qlik uses the labels

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

retko1985 — Mon, 20 Nov 2017 11:12:17 GMT

This is quite confusing. I am able to get KEY or PERFECT KEY, but not the PRIMARY KEY.

I made it similar as you said, filling the null. I also changed keys so I will not have the perfect sequence (some sources say that perfect key is only for perfect sequence using autonumber). And I still see the Perfect key, even though I did not use autonumber, and I don't have the perfect sequence:

This probably doesn't matter much. I just wanted to know how it works in Qlikview....

But if someone can achieve a PRIMARY KEY label on some example, please post.

Thank you.

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

jyothish8807 — Mon, 20 Nov 2017 11:22:38 GMT

Hi Retko,

Most of the time it is not possible to achieve a perfect primary Key, only think you have to make sure is the the master table should have all the key value present with in with respect to other tables it is connected.

Br,

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

jyothish8807 — Mon, 20 Nov 2017 11:25:57 GMT

Having perfect key is not depended on sequence or Autonumber.

If your master table and the other supporting table have exactly same unique key values then its subset ration becomes 100% and it will become a perfect key.

Correct me guys if i am wrong

Br,

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

swuehl — Mon, 20 Nov 2017 12:21:08 GMT

Just remove the line with the NULL in table Invoices and add a line with a new InvoiceID in InvoiceDetails.

This should make your InvoiceID in Invoices a Primary Key.

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

retko1985 — Mon, 20 Nov 2017 15:11:24 GMT

Ok, this works.

So I guess this is should be like this:

Perfect key - subset ration is 100% AND all keys are unique.

Key - Keys are NOT unique.

Primary key - Subset ration is less than 100% AND all keys are unique.

Correct?

Thank you.

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

swuehl — Mon, 20 Nov 2017 15:41:57 GMT

That's basically what I tried to express in my previous post.

Key - Keys are NOT unique or some records show NULL

Primary key - Subset ratio is less than 100% AND all records show unique values (i.e. also no NULL)

Perfect key - subset ratio is 100% AND all records show unique values

subset ratio is the share of symbols used in the table compared against all symbols of the key field (looking at all tables)

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

retko1985 — Mon, 20 Nov 2017 15:52:41 GMT

Great, thank you for detailed clarification..

So maybe for the next generations

Re: Situation when I get A primary Key, instead of Key or Perfect Key.

ysj — Mon, 22 Jan 2018 13:50:51 GMT