How to quickly find out which field is not unique?
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)?
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.
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 ...
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...
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.