Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Champion
Champion

Identifying a unique key in a table

Hi,

I am trying to find out what fields I need to generate a unique key for a table.

Is there any faster way to do this? Currently, I do the following:

- I load all the fields from that table

- I create a straight_table_diagram with

     - different fields as dimension (that is my "workbench")

     - a COUNT() of the rows as expression

       => I sort the chart by that COUNT (by moving it to the top of the list and setting it to "numeric value desc."

<=> The issue is, every time I add just one more field to the dimensions, the sorting list resets and my COUNT() is somewhere around the last place again, so I have to move it up again.

That is quite a lot of work, especially as I have no idea what field(s) would make up a key which uniquely identifies one record.

=> Is there any faster way?

Thanks a lot!

Best regards,

DataNibbler

Tags (1)
17 Replies
Highlighted
Master II
Master II

Re: Identifying a unique key in a table

Why not make a straighttable with

$Field as dimension

and

FIELDVALUECOUNT($Field) as expression.

HTH Peter

Highlighted
Champion
Champion

Re: Identifying a unique key in a table

Hi Peter,

that is an interesting function - but no different from a COUNT(Line_ID), is it? When I have hit upon a field that will serve as a unique key, both mine and yours will be 1, no?

Best regards,

DataNibbler

Highlighted
MVP & Luminary
MVP & Luminary

Re: Identifying a unique key in a table

Hi DataNibbler,

you could try to access always the first dimension with:

count(GetObjectField(0))

But this meant not absolutely that you counted a unique value - maybe you need to combine several fields or you creates a unique key within the script, maybe with rowno().

- Marcus

Highlighted
Specialist
Specialist

Re: Identifying a unique key in a table

How about loading all variables in a listbox first. If you then qlick on each one of them, there is a mention in the bottom right corner of the number of counts for that variable. If you compare it to the total of lines (which you know from loading), you get I think a faster insight into what you need.

Johan

Highlighted
Master II
Master II

Re: Identifying a unique key in a table

Hi DataNibbler,

using $Field will give you a universal tool, so you do not need to adjust each time to new fields or fieldnames.

Then you only need to compare with the total number of rows - typically the top value.

Peter

Highlighted
Champion
Champion

Re: Identifying a unique key in a table

Hi Peter,

now I see what you are proposing.

That is indeed helpful - if I select the table in another listbox with $Table and then compare with the value in another listbox with $Rows, I can see that - if one single field has a Fieldvaluecount equal to that, that is a unique key - but what if there is no single field that will serve as a unique key? Can I somehow quickly create different combinations of 2 or more fields?

Highlighted
Not applicable

Re: Identifying a unique key in a table

Hi,

I'm using Frequency property in order to identify unique fields. Just add field to sheet and select "Show Frequency".

Frequency1.png

Sort by frequency. be aware, It doesn't work for key field

Frequency2.png

Highlighted
Champion
Champion

Re: Identifying a unique key in a table

Hi Taras,

thank you too. However, these approaches help only if there is one single field that serves as a unique key - rowNo() is always a candidate, but not really workable, I cannot link different tables using rowNo() 😉

I guess I'll just have to continue working with a straight_table_diagram and add fields to the dimensions until I have hit upon a unique key - and then remove as many fields again as possible so I end up with just the ones I really need to make up a unique key.

Best regards,

DataNibbler

Highlighted
MVP & Luminary
MVP & Luminary

Re: Identifying a unique key in a table

Hi DataNibbler,

if you really need a unique key you will have to create one within the script like above mentioned then within the gui you must know which combination of fields would be create this key and therefore I think it's not very practical.

What is the aim behind this unique key count?

- Marcus