Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Whatever works Friedrich, sometimes "dirty" solutions are all we really need
Best regards
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.
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
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.
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.
Show frequency is the good option to find the number of duplicate values in the field
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 ...
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 ....
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...
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