Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

How to quickly find out which field is not unique?

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

1 Solution

Accepted Solutions
dapostolopoylos
Creator III
Creator III

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

Best regards

Father/Husband/BI Developer

View solution in original post

12 Replies
facettti
Partner - Contributor II
Partner - Contributor II

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.

kfoudhaily
Partner - Creator III
Partner - Creator III

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

QlikView Qlik Sense consultant
dapostolopoylos
Creator III
Creator III

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.

Capture.JPG

Capture111.JPG

Father/Husband/BI Developer
datanibbler
Champion
Champion
Author

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.

manoj217
Creator III
Creator III

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

datanibbler
Champion
Champion
Author

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

prieper
Master II
Master II

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

dapostolopoylos
Creator III
Creator III

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

Father/Husband/BI Developer
marcus_sommer

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