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

    Count of Unique Values

    CountryBoy1

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

        Thanks for your help.

         CountryBoy1

       

       

       

       

       

        • 1. Re: Count of Unique Values
          raybaudi

          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
            CountryBoy1

            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
               for your help.  Sincerely CountryBoy1

             

            • 3. Re: Count of Unique Values
              raybaudi

              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
                CountryBoy1

                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
                  raybaudi

                  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
                    CountryBoy1

                    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.
                         Thanks for your help.  CountryBoy1

                     

                     

                    • 7. Re: Count of Unique Values
                      philmodjunk

                      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
                        CountryBoy1

                        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.

                             I appreciate your help.  Your answer is a solution
                         and I will mark it as such.

                             Sincerely, CountryBoy1

                         

                         

                        • 9. Re: Count of Unique Values
                          aikiko

                          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.

                           

                          Thank you in advance,

                          Aikiko 





                           

                          • 10. Re: Count of Unique Values
                            raybaudi

                            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
                              aikiko

                              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
                                aikiko

                                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
                                  raybaudi

                                  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
                                    comment_1

                                    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