topic Count IF in an expression in QlikView

Count IF in an expression

bazzaonline — Thu, 16 Oct 2014 12:07:27 GMT

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

Re: Count IF in an expression

simondachstr — Thu, 16 Oct 2014 12:10:18 GMT

Use Set Analysis

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

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

Re: Count IF in an expression

markodonovan — Thu, 16 Oct 2014 12:13:07 GMT

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

Re: Count IF in an expression

Thu, 16 Oct 2014 17:11:38 GMT

Hi Paul,

Try this:

Yours,

William

Re: Count IF in an expression

simondachstr — Thu, 16 Oct 2014 21:41:25 GMT

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

Re: Count IF in an expression

Thu, 16 Oct 2014 22:36:54 GMT

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

Re: Count IF in an expression

simondachstr — Fri, 17 Oct 2014 08:44:47 GMT

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-aggregations

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

Re: Count IF in an expression

Fri, 17 Oct 2014 17:05:20 GMT

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