Identifying a unique key in a table

[datanibbler]

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

[prieper]

Why not make a straighttable with

$Field as dimension

and

FIELDVALUECOUNT($Field) as expression.

HTH Peter

[datanibbler]

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

[marcus_sommer]

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

[puttemans]

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

[prieper]

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

[datanibbler]

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?

[#8]

Hi,

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

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

[datanibbler]

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

[marcus_sommer]

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