topic Re: How can I turn my Count (Distinct If into a set expression? in QlikView

How can I turn my Count (Distinct If into a set expression?

robinwiman — Mon, 26 Feb 2018 13:16:41 GMT

Is it possible to turn the Count (Distinct expression into a set?

I really hate the performance drainer that count distinct is.

Here is an expression:

Count (Distinct if(Counter='1'and Shop <> '1101' and _YTD_WTY='1',Kvitto))

I'm thinking:

Count ( {< Counter = {'1'}, Shop -= {'1101'} >} Kvitto*_YTD_WTY)

The thing is that when I use the set expression (which works btw) my data changes. I use the formula to calculate number of transactions (Kvitto translates into Receipt in Swedish). So the whole expression is then later divided by visitors.

I can see the changes are small but nonetheless there. Some results change from 2.3% to 2.4% and similar.

Where am I going wrong with this?

Re: How can I turn my Count (Distinct If into a set expression?

Anil_Babu_Samineni — Mon, 26 Feb 2018 13:20:31 GMT

This this way?

Count ( {< Counter = {'1'}, Shop -= {'1101'}, _YTD_WTY={1} >} Kvitto)

Re: How can I turn my Count (Distinct If into a set expression?

sunny_talwar — Mon, 26 Feb 2018 13:25:39 GMT

Are you may be missing a DISTINCT?

Count(DISTINCT {< Counter = {'1'}, Shop -= {'1101'}>} Kvitto*_YTD_WTY)

Re: How can I turn my Count (Distinct If into a set expression?

robinwiman — Mon, 26 Feb 2018 13:31:23 GMT

Yeah, this works!

So just a follow-up question; how would the two expressions compare performance-wise? Isn't Count(DISTINCT quite heavy to calculate ? Albeit the removed If-expression should do its part.

Re: How can I turn my Count (Distinct If into a set expression?

sunny_talwar — Mon, 26 Feb 2018 13:36:33 GMT

It is a myth as suggested by HIC

A Myth About Count(distinct …)

Re: How can I turn my Count (Distinct If into a set expression?

robinwiman — Mon, 26 Feb 2018 13:39:29 GMT

Exactly what I needed thanks Sunny.