Creating counter fields to avoid Count Distinct

Using a Count function is a very common use case in any type of reporting. However, it is also a very laborious calculation, requiring many more CPU cycles than, say, a Sum function. This is because a function like Sum is actually a very low-level function, operating almost at the CPU level, whereas a function like Count is more high-level, operating at the application code level. On a multi-threaded system (which means almost all systems nowadays), the Sum function will run across all cores whereas the Count function will only calculate on one thread.

If we can avoid Count functions, especially a Count function with a Distinct clause, then we will have a more efficient application.

Getting ready

Generate QVDs created in the Reducing the number of distinct values recipe.

How to do it…

These steps show you how to create counter fields to avoid Count Distinct:

1. Create a new QlikView file in the same folder as the QVDs. Load the following script:

   LOAD...