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

[Anonymous]

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

[datanibbler]

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 ...

[marcus_sommer]

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

[datanibbler]

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.

[datanibbler]

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.

[stevedark]

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

[datanibbler]

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

[stevedark]

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