Count Distinct combination of 2 or more fields

[#1]

Hello

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?

[#2]

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.

[jcamps]

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.