2 Replies Latest reply: Jan 22, 2015 3:54 AM by Jordi Camps RSS

    Count Distinct combination of 2 or more fields

    Павел Никифоров



      I need to count all existing combinations of values in 2 fields.


      Something like this in SQL:


      count(DISTINCT Field1, Field2)


      How can I do this?

        • Re: Count Distinct combination of 2 or more fields
          Srikanth P

          Hi Pavel, Try below expression with concatenation on the fields.

               Count( DISTINCT FIELD1&'|'&FIELD2)


          However this is not good approach if you have more data. Please create the new number field with auto number function in the script.


          LOAD * ,  AUtonumber(FIELD1&'|'&FIELD2) AS FIELD1_2_COUNTER ;


          Use COUNT (DISTINCT FIELD1_2_COUNTER) in your expression.

            • Re: Count Distinct combination of 2 or more fields
              Jordi Camps

              Just as another approach... you might not be willing to trade the additional memory for the new field in the previous answer (which might impact performance in other calculations), for the performance gain in an uncommon calculation. That might be the case if:

              - There are many distinct combination values

              - The table is very large,

              - The count(distinct) calculation is rarely used in the document,


              If both fields were already numeric (they probably are if they are autonumber dimension %keys), and they belong in the same table, you can try using

                   count(FIELD1*10000 + FIELD2)


              which is much much faster than counting string values. The number by which you multiply must be larger than the largest value in FIELD2, otherwise you'd get wrong values.


              A still faster alternative would be to do a binary shift, first you need to get the binary length of field2 with something like;


                 let vBits = len(num(FieldValueCount(Field2),'(bin'));


              Then you can do


                 count(FIELD1<<bits+FIELD2) which should still be faster. Obviously not as fast as creating a new autonumber field but probably fast enough for a seldom used expression.