topic Identifying a unique key in a table in QlikView

Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 13:34:19 GMT

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

Re: Identifying a unique key in a table

prieper — Tue, 10 Mar 2015 14:34:05 GMT

Why not make a straighttable with

$Field as dimension

and

FIELDVALUECOUNT($Field) as expression.

HTH Peter

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 14:41:27 GMT

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

Re: Identifying a unique key in a table

marcus_sommer — Tue, 10 Mar 2015 14:50:15 GMT

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

Re: Identifying a unique key in a table

puttemans — Tue, 10 Mar 2015 14:53:14 GMT

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

Re: Identifying a unique key in a table

prieper — Tue, 10 Mar 2015 15:05:11 GMT

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

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 15:19:58 GMT

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?

Re: Identifying a unique key in a table

Tue, 10 Mar 2015 15:21:11 GMT

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

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 15:29:05 GMT

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

Re: Identifying a unique key in a table

marcus_sommer — Tue, 10 Mar 2015 15:29:51 GMT

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

Re: Identifying a unique key in a table

Anonymous — Tue, 10 Mar 2015 15:35:31 GMT

I find stevedark 's QlikView App - Generic Data Profiler useful.

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 15:36:36 GMT

Hi Marcus,

easy. The point is, there is another table I'm going to LOAD after this one and I need to JOIN or at least link the two somehow - which is, afaIk, only possible (or rather, sensible) if at least one of the tables has a unique key, so I can build a proper 1:N relation. I don't really trust N:N relations ...

Re: Identifying a unique key in a table

marcus_sommer — Tue, 10 Mar 2015 15:48:50 GMT

Hi DataNibbler,

ok. it's a scripting issue - this wasn't quite clear because you tried to find your key-information within the gui. I think you will have first understand what kind of data your tables have to find the correct field-combination which both tables supported. But it could be that if the quality of the data are bad you could fail with this approach.

Maybe it's an alternatively to concatenate and/or map these tables to a single fact-table.

- Marcus

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 15:52:59 GMT

Hi Bill,

that sure looks good. I'll have a closer look at it asap.

The problem is, I have 2 tables´, both of which having to do with the individual ITEM_Nr.

<=> In one, this is not a unique key, but a combination of the Item_Nr and the Package_ID (the box that Item is in) is

<=> I don't have the Pkg_ID in the second table, so I cannot use that.

The Item_Nr itself is not unique in either of the tables, so using that to link or JOIN the two might lead to unexpected results I do not really want.

Well, I'll have a chat with a colleague tomorrow, maybe he can help me.

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 15:56:33 GMT

Hi Marcus,

well, I am trying to reduce the nr. of tables I have as far as possibe.

As to the kind of data - well, the latest table I have to take up into the data_model is called >> Item_Master <<, which is why I wonder that the Item_Nr is not unique in that table.

Well, maybe that colleague, who has more to do with the database than myself, will be able to help me find the key that I can use to link those two tables.

Re: Identifying a unique key in a table

stevedark — Tue, 10 Mar 2015 16:16:22 GMT

If you just let the two tables associate then it will probably be fine (depending on the expression you need to perform over which dimensions).

An association (i.e. two separate tables in the QlikView data model with a line between them) is not like a JOIN in SQL. Values that are in the table without Pkg_ID will not be duplicated to accommodate the JOIN.

Summing values from the second table with dimensions from the first will work fine.

Summing values from the first table in a table with any Package information in it will give strange results - the total will be correct but adding up the individual rows will give a different table as some values will be double counted.

I hope that makes sense?

Steve

Re: Identifying a unique key in a table

datanibbler — Tue, 10 Mar 2015 16:24:31 GMT

Hi Steve,

yes, I guess it does.

Which field do you think I could use to associate the two tables? Item_Nr?

It just means that if I cannot remove that issue, I (and any potential other users) will have to be very careful about which kind of calculations are done on which table.

I must admit I have a bit of a headache about such a thing ... I will try to find a way to make it more foolproof. You know, people have a way of very quickly sniffing out the kind of things they should not be trying to do and doing them 😉

Best regards,

DataNibbler

Re: Identifying a unique key in a table

stevedark — Tue, 10 Mar 2015 22:10:28 GMT

From what I can see associating on the Item Number would be fine.

It's always an issue letting people that don't fully understand the data model loose on your beautifully crafted QlikView app. I think that what is important there is that there is some kind of QA, to ensure that people are not creating objects that could be misleading.

I always prefer it when the development is left to developers and business requirements are handed over in specification documents. Things don't always go that way though.

Good luck with it.

Steve