Count IF in an expression

[bazzaonline]

Hello,  I want to right an expression which basically say, if the field 'location' equals 'Halifax' count the number of instances.

What formula do I need to write?

Thanks Paul

[simondachstr]

Use Set Analysis

=count({<location={'Halifax'}> FIELDTOCOUNT)

Depending on the fieldtocount and your requirements you may or may not need to use distinct.

[markodonovan]

Hi Paul,

Try this:

count({<location={'Halifax'}>} fieldtocount)

The field to count could be the location field.

You could also create a field for counting set to 1 in your table ie:

1 as location_count

Then use the following (I think there are performance benefits to using sum):

sum({<location={'Halifax'}>} location_count)

Hope this helps.

Thanks

Mark

http://www.techstuffybooks.com

[#4]

Hi Paul,

Try this:

Yours,

William

[simondachstr]

THIS is bad practice William - Have a look at the QlikView Reference Manual 11 Appendix C.4 for more information.

[#6]

Hi Martin,

Thank you for your comment. I looked at the reference manual and I believe it's bad practice because it's expensive to have text comparisons in the condition of if statements. I wasn't aware, so thanks for pointing this out! Is there more to it or is this basically the reason? I just want to make sure I've understood this properly.

Also, in light of this discovery, what would be the performance difference between 1. flagging the value 'Halifax' in the script with the number 1 and using the flag field instead in the condition of the if statement (like the reference manual suggests), and 2. using set analysis like you suggested above?

Yours,

William

[simondachstr]

These are very good questions Williams and to get an answer one needs to dig deeper into the QlikView calculation engine. The two documents below by Henric summarize very well what's going on in conditional calculations and how the performance is affected:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/07/performance-of-conditional-aggregation...

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/12/count-or-count-distinct

[#8]

Hi Martin,

Thank you for posting the links to HIC's articles - very informative indeed. What really stood out to me was knowing that, even though set analysis is by far the best option for large data sets, it may not be so for small ones, because its overhead may be larger than the performance gain. So it's back to the fundamentals: knowing what tool to use and when - as an engineer, I love it.

Anyway, I'd like to take a step back to ask Paul Barrett if his question is now answered. Paul, if you can give us an update that would be very helpful.

Cheers gentlemen,

William