1 2 Previous Next 15 Replies Latest reply on Jun 1, 2010 2:15 AM by aikiko

# Count of Unique Values

### Title

Count of Unique Values

### Post

I have a field with varying integer values.  Need to

know how many different number values in the data.

As example, could have many records with value 2,

many records with value of 4, many records with value of 8.

Need a function or functions that can tell me there are
just 3 different numbers (2, 4 and 8).

CountryBoy1

• ###### 1. Re: Count of Unique Values

ValueCount ( ValueListItems ( Get ( FileName ) ; "YourValuelist" ) )

where "YourValuelist" is the name of a list made by values of that field.

• ###### 2. Re: Count of Unique Values

Dear raybaudi

I tried your code but get a value of 0.  I do not have an actual
valuelist that I'm trying to calculate, I have just a numeric field.  I am

using  List (xname)  as the YourValuelist argument.  "xname" is the

name of the field.  Can you give me further direction ?  Thanks

• ###### 3. Re: Count of Unique Values

CountryBoy1 wrote:

I do not have an actual
valuelist ...

So:

1) Create a new value list with values coming from that field

2) Create a calculation field with the given calc.

• ###### 4. Re: Count of Unique Values

Dear ribaldi:

The field, FieldA, is a (numeric) calculation field that is unstored (calculated only as needed).

When I built the valuelist, I had to change FieldA to be stored and use indexing, else the
valuelist couldn't be built.

The global field, FieldB, now calculates the number of unique values in FieldA.  However

it is not dynamic.  I perform Find operations on the records and this changes the number of unique

values in FieldA.  The calculation of FieldB does not reflect the change of  the FieldA records

Is there something else that can be done ?  Thanks much for your help.

CountryBoy1

• ###### 5. Re: Count of Unique Values

So we can't use a value list, having the fieldA an unstored value.

Are you sure that that field MUST have an unstored result ?

How many records are involved into this request ?

• ###### 6. Re: Count of Unique Values

Dear daniele:

Actually I was wrong.  It is a calculated field and I do have it stored and indexed.

Nevertheless, when I build the valuelist, I always get the group of values from the

entire data set and cannot get it to just use values from a found set.  The number

of records is very few right now but could grow to be several hundred.

I may have to write a function to calculate the number of unique values of a

field of a found set.  Even trying to use the FIND mode, I see no way of it giving
just unique values.  It can give duplicate and non-diplicate values but that is of

no use.  It seems like Filemaker Pro needs a "unique" function can be used,

perhaps as a Summary function.

• ###### 7. Re: Count of Unique Values

There is a way to do this with summary fields that has been posted to the forum previously.

Define a count of summary field that counts your number field, I'll call it sGroupCount.

Define a calculation field: cFraction: 1/Get Summary (sGroupCount ; YourNumberFIeld )

Define a second summary field sUniqueCount as the the Sum of cFraction.

This works on the found set as long as your sort your records by YourNumberField.

• ###### 8. Re: Count of Unique Values

Dear PhilmodJunk

I do have some blank fields and I prefer not to sort

the data by this numeric field.  Therefore I think I will

write a function to give me the number of unique values.

and I will mark it as such.

Sincerely, CountryBoy1

• ###### 9. Re: Count of Unique Values

Dear Daniele,

I have a similar problem to that listed above, and I think that your calculation is what I need. The calculation being:

ValueCount ( ValueListItems ( Get (FileName) ; "YourValueList" ) )

However I cannot get it to work.

Briefly, I want to count the number of times the following values appear in a field.

Field name: CXR

type: text

value list name: 'CXR result'

value list values: Normal, Active TB, nonactive TB, other

In my 40000 chest x-rays, I would like to have 4 extra calculation fields that would follow the text:

1) The number of Normal CXRs is <insert field to calculate number of normal CXRs>

2) The number of Active TB CXRs is <insert field to calculate number of Active TB CXRs>

3) The number of nonactive TB CXRs is <insert field to calculate number of nonactive TB CXRs>

4) The number of other CXRs is <insert field to calculate number of other CXRs>

The calculation I think I need for these extra 4 calc fields is something like:

ValueCount ( ValueListItems ( Get (FileName) ; "CXR result" ) )

but I'm not sure of the calculation syntax to select for 'Normal', Active TB etc.

I think I am on the right path, but any assistance would be much appreciated for a newbie like myself.

Aikiko

• ###### 10. Re: Count of Unique Values

No, you aren't on the right path.

A value list keeps only different values from all values that a field can assume in the DB

.

You can use a calculation like this:

ValueCount ( FilterValues ( List ( related:: CXR ) ; "Normal" ) )

to retrive the count of Normal

The relationship must have the X operator to obtain  a list of all the values.

• ###### 11. Re: Count of Unique Values

Dear Daniele,

Thank you for leading me down the correct path, but I need further direction.

What do you mean by  'related::CXR' in the following calc?

valueCount ( FilterValues ( List ( related:: CXR ) ; "Normal" ) )

and can you explain what you mean by 'X operator'?

thanks again

Aikiko

• ###### 12. Re: Count of Unique Values

Dear Daniele,

Thank you for leading me down the correct path, but I need further direction.

What do you mean by  'related::CXR' in the following calc?

valueCount ( FilterValues ( List ( related:: CXR ) ; "Normal" ) )

and can you explain what you mean by 'X operator'?

thanks again

Aikiko

• ###### 13. Re: Count of Unique Values

You'll need to create a self-join relationship with the cartesian operator ( X ) between two fields surely not empty. ( I suggest to keep the ID of the record ).

So, for example, if you have a table named TABLE, you'll need to:

1) duplicate it in the relationship graph ( FileMaker will give it the name: Table 2 )
2) draw a line between Table:: ID and Table 2:: ID
3) change the operator from "=" to "X"

the calculation will be something like:

ValueCount ( FilterValues ( List ( Table 2:: CXR ) ; "Normal" ) )

• ###### 14. Re: Count of Unique Values

There is another option, which may be simpler (and faster): define a summary field as Count of [any field that cannot be empty], and produce a report summarized by CXR.

1 2 Previous Next