Re: Count Distinct combination of 2 or more fields
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.