7 Replies Latest reply on May 24, 2012 8:27 AM by philmodjunk

    Find similar values in a List()

    FH

      Title

      Find similar values in a List()

      Post

      Hi,

       

      I would like to calculate in a list that I populate with the List() function from different fields, which number has the most occurences, eg. for the list

      2 3 2 5 1 5 2 1 

      The result should be 2, since this value has the most occurences compared to the other values.

      I wonder if it is possible to calculate this with a plain calculation or a custom funcion, not using a script.

      Thanks,

       

      Francis

        • 1. Re: Find similar values in a List()
          philmodjunk

          A recursive custom function could do this.

          // Mode ( ValueList ; Value )
          //
          // valueList    :  A return separated list of values. values can be text or number
          // value         :  Interim value that passes the current mode candidate to the next recursive call of the function.
          //
          Let ( c = ValueCount ( FilterValues ( ValueList ; Getvalue ( valueLIst ; 1 ) ) ) ;
                    Case ( IsEmpty ( ValueList ) ; Value ; // return current data in Value if list is empty
                              c > Value ; Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; GetValue ( ValueList ; 1 ) ) ;
                              Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; Value )
                             ) // case
                ) // let

          Your originating function call would look like this:

          Mode ( Field, variable or list calculation ; "" )

          • 2. Re: Find similar values in a List()
            philmodjunk

            Ooops. I was mulling this over on the drive in to work and realized that I had a major bug in that function. Use this version instead:

            // Mode ( ValueList ; Value; Cnt )
            //
            // valueList    :  A return separated list of values. values can be text or number
            // value          : Item value that passes the current mode candidate to the next recursive call of the function.
            // Cnt             : The count of the items of the currently nominated value
            //
            Let ( c = ValueCount ( FilterValues ( ValueList ; GetValue ( valueLIst ; 1 ) ) ) ;
                      Case ( IsEmpty ( ValueList ) ; Value ; // return current data in Value if list is empty
                                c > Cnt ; Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; GetValue ( ValueList ; 1 ) ; c ) ;
                                Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; Value ; Cnt )
                               ) // case
                  ) // let

            And the originating function call would look like:

            Mode ( Field, variable or list calculation ; "" ; "" )

            • 3. Re: Find similar values in a List()
              FH

              Hi Phil,

               

              thanks a lot for the function and the reworked function which functions. Being new to custom functions, it is not yet clear to me, how I can get returned the count value, if yo might comment on that it would be helpful. Thanks,

               

              Francis

              • 4. Re: Find similar values in a List()
                philmodjunk

                The function, as written, does not return the count, only the value that appears most frequently in the list.

                Which do you want? (Functions return single values, but that single value could be a two value list, so both returning both the value and the count is possible.)

                • 5. Re: Find similar values in a List()
                  FH

                  If a function can only return one value, I think the best would be the return as a list of two values, seperated by any common denominator. That would be great.

                  • 6. Re: Find similar values in a List()
                    FH

                    Could we do it like this?


                    Let ( c = ValueCount ( FilterValues ( ValueList ; GetValue ( valueLIst ; 1 ) ) ) ;
                              Case ( IsEmpty ( ValueList ) ; Value&"denominator"&Cnt ; // return current data in Value if list is empty
                                        c > Cnt ; Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; GetValue ( ValueList ; 1 ) ; c ) ;
                                        Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; Value ; Cnt )
                                       ) // case
                          ) // let

                    • 7. Re: Find similar values in a List()
                      philmodjunk

                      Looks like that would work. "denominator could just be " ".  The LeftWords and RightWords functions could extract the two individual values from the value returned.

                      You might also use this as the part in red: List ( Value ; Cnt )

                      Then Set Variable [ $Mode ; value: Mode ( $List ; "" ; "" ) ]

                      puts a return separated list of the two returned values into a variable named $Mode.

                      GetValue ( $Mode ; 1 ) returns the value

                      GetValue ( $Mode ; 2 ) Returns the count