topic Re: How to quickly find out which field is not unique? in QlikView

How to quickly find out which field is not unique?

datanibbler — Fri, 29 Jun 2018 09:12:33 GMT

Hi,

quite often I want to test whether a keyfield is unique in a given table. When I find out that it is not - when I select one key, I get several records - how can I find out which field has several distinct values (apart from looking through all the fields which can take a while if a table is broad)?

Thanks a lot!

Best regards,

DataNibbler

Re: How to quickly find out which field is not unique?

facettti — Fri, 29 Jun 2018 09:21:07 GMT

Hello,

Go to 'Data model viewer' and click on particular field in the model you are interested in.

On the bottom of the screen in 'Preview' you can find information like field density , subset ratio and info whether the field has duplicates.

Re: How to quickly find out which field is not unique?

kfoudhaily — Fri, 29 Jun 2018 09:22:36 GMT

there are many techniques out there to this;

using SQL or Qlik you may compare count(field) to count(distinct(field))

you may use "EasyQlik QViewer" that gives good indication about data through it's metadata

also in qlik datamodel when you go to the table Viewer tool you may identify usefull information about your key if you show the information of "information density" and "subset ratio"

if a key is perfect then the indication "perfect key" should appear on the Table Viewer.

hope it's helpfull

Re: How to quickly find out which field is not unique?

dapostolopoylos — Fri, 29 Jun 2018 10:21:35 GMT

You could try to create a listbox with that keyfield and select the Frequency checkbox. That will show a number next to every value of the listbox with the number of the appearences of every value.

Re: How to quickly find out which field is not unique?

datanibbler — Fri, 29 Jun 2018 12:15:32 GMT

Hi Dimitrios,

that doesn't work yet: I have a listbox $Field (the system-generated one) with the "show frequency" checked - every field has the value 1, whether or not I select it, but I know - and I can see in another listbox - that to one instance of the key, there are three values in one specific field - that way I can quickly see if a method works or not, and this one doesn't - yet ...

I have also tried an IF-fct, >> IF(Getpossiblecount($Field) > 1, 'not unique', 'unique') << - didn't work either ...

P.S.: With >>Getalternativecount<< it won't work, either - then I always get a value of 3 (for every field) because there are 3 records for one instance of the key logically - but the issue is only one field.

Re: How to quickly find out which field is not unique?

manoj217 — Fri, 29 Jun 2018 12:21:51 GMT

Show frequency is the good option to find the number of duplicate values in the field

Re: How to quickly find out which field is not unique?

datanibbler — Fri, 29 Jun 2018 12:26:22 GMT

Well, no, it is not - as I told you, in my instance the frequency of every field (with one key selected) is always 1 - but I can see in another listbox that in one specific field, there are 3 distinct values resulting in 3 records for this key ...

Re: How to quickly find out which field is not unique?

prieper — Fri, 29 Jun 2018 13:04:25 GMT

Hi,

I once copied the attached object, but have not recorded, who created it.

You may grap the idea from there.

Please be aware that it needs to calculate quite long in a bigger database.

Regards Peter

Edith says that it might be better to populate the file with some sample data ....

Re: How to quickly find out which field is not unique?

dapostolopoylos — Sat, 30 Jun 2018 08:45:16 GMT

The frequency in the $Field listbox shows the number of appearences of a field in your Data Model.

Logically if you have set up a data model based on a star schema all your fields in the $Field listbox should have frequency equal to 1 except from those which are keys between the fact table and the dimension tables and would have frequency equal to 2.

What i want to say is that there is not a solution that will cover all implementation cases because there are many options in constructing your data model.

If you could provide a sample app that reflects the issue that you are facing to play around it would be easier to find more solid solutions...

Re: How to quickly find out which field is not unique?

marcus_sommer — Sat, 30 Jun 2018 13:19:22 GMT

I suggest using a table-chart with $Table and $Field as dimensions and:

=if(count(total <$Field> $Field) > 1, 'Yes', 'No') // to identify key-fields

=FieldValueCount($Field)

=$Rows

=$Rows - FieldValueCount($Field)

as measures. It will give a good overview about the records of the tables and the distinct values of each field whereby this approach don't work to give a direct answer for the key-fields because they exists in multiple tables and their distinct values overall could be higher as the records of each single table and you don't know which values come from which table.

I think to get this you will need to include any table-specific condition within an expression which counts directly over the table and not with the systemfields. Not yet checked for this kind of query - you might be able to gather this information through the qvw meta-data.

I believe both ways should be possible but they might be not so easy and quickly like you mentioned it within your question. Therefore it will be probably easier to look for these information within the table-viewer.

- Marcus

Re: How to quickly find out which field is not unique?

marcus_sommer — Sat, 30 Jun 2018 13:23:54 GMT

I think this tool is from Ralf Becher and here was already a quite similar discussion: Re: Chart with count of $Field values.

- Marcus

Re: How to quickly find out which field is not unique?

datanibbler — Mon, 02 Jul 2018 08:21:47 GMT

Hi Dimitrios,

I cannot provide you a sample app, but I can tell you how I would do it for now, not knowing a faster way:

I would have a table_chart with the internal key (identical to the key, but not linked to other tables) as the dimension and >> COUNT([fieldname] << (as # of distinct values) for every field as measures.

That does not save me much time or effort compared to just looking through a heap of listboxes to find which field is not unique, but it does return me exactly what I want - most fields will only have a nr. 1 here, one or several will have a value >1.

Best regards,

DataNibbler

Oops, forgot the DISTINCT - it should be >> COUNT(DISTINCT [fieldname]) << - if I add that as expression once for every field, that will not save me much effort, but it will return just what I'm looking for.

Re: How to quickly find out which field is not unique?

dapostolopoylos — Mon, 02 Jul 2018 12:40:21 GMT

Whatever works Friedrich, sometimes "dirty" solutions are all we really need

Best regards