I was recently reviewing a problem where a chart Count() function produced differing results between QV 8.5 and 9. The field being counted was a key field. Counting a key field without using DISTINCT, especially a one-to-many key, can produce ambiguous results and should be avoided.
The recommended approach is add a counter field and sum() that field. For example, in an Orders table, add the field:
1 as OrderCounter
and then count Orders with:
sum(OrderCounter)
Yes, count(OrderCounter) will work as well. The Qlikview literature still states that sum() is faster and preferable to count(). John Witherspoon recently showed me some tests that demonstrate count() being faster than sum() in Version 9, so it’s possible that recommendation should be examined if you are working on a very large application.
-Rob
Posting my comment to you from back when we looked into this issue as an alternate perspective:
“I think it’s fine to count(distinct key). And it should be fine to count(key) if the key is 1:1 with some other table (but if so, you should probably join the two tables). But I agree that we shouldn’t count(key) otherwise, as the answer seems by definition to be, well, undefined. In that case, count something OTHER than the key, or sum a flag.
As for counting vs. summing… It SHOULD be easier for a computer to count than to sum. To test it, I created a table like this:
LOAD recno() as ID
,1 as Flag
AUTOGENERATE 20000000
;
Then had a count(ID) chart on one tab, and a sum(Flag) chart on another tab. Here are the resulting calc times:
count(ID) sum(Flag)
QV 8.5 1576 ms 1031 ms
QV 9.0 SR2 16 ms 844 ms
If I can trust calc times, it appears that count WAS slower for some reason, but that this has been fixed in 9.0. The difference in 8.5 isn’t very large, so we can get away with using count(). The difference in 9.0 IS large, so you’d want to use count() instead of sum(). Granted, a simple test on simple data like this isn’t conclusive, but I think it’s at least suggestive. I do plan to continue using count when all I’m doing is counting.”