topic Count() behaving as count(distinct)?? in QlikView

Count() behaving as count(distinct)??

Tue, 25 Apr 2017 09:23:24 GMT

Hi. I'm not a regular Qlikview user, so tend to stick to fairly simple functionality. I am trying to compare 2 very similar data sets and finding the behavior of Count() to be different (one shown on AccessPoint,(QlikviewV11) and one on my Personal Edition (V12) - although I wouldn't have thought that would make a difference...).

The Personal Edition seems to be treating a count() as a count(distinct) - as you can see here it returns a count of 1 when there are plainly multiple records that match:

For comparison, the version on AccessPoint gives what I would expect:

I can't find anything on the community/help to explain this, can anyone help please?

Re: Count() behaving as count(distinct)??

mayankraoka — Tue, 25 Apr 2017 09:29:27 GMT

Hi,

Is it possible to share the application here?This will help to solve the issue in a better way.

Regards,

Mayank

Re: Count() behaving as count(distinct)??

swuehl — Tue, 25 Apr 2017 09:34:58 GMT

Is this field a key field in your model? (you shouldn't count a key field without distinct qualifier)

And are you looking at the exact same data set, no reload performed inbetween? No section access involved?

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 09:52:20 GMT

Hi Stefan

Thanks for your input. I haven't specified it as a key (not sure how I'd do that), and it's not a link to another table, but the Internal Table View shows "[Perfect Key]" after the field name in the owning table. I presume this is determined by Qlikview automatically when the data is loaded..?

I'm trying to count the number of records for each value of this field - surely that should be achievable? I always want to count the number of records with a distinct feature. I've been using count() in this way for years without coming across this before...?

No, it's not exactly the same data set - that's why I was doing the comparison. I'm afraid I don't know what 'section access' refers to.

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 09:54:31 GMT

Sorry Mayank - the data is confidential. If this isn't as simple to resolve as I hope, I'll have to try to replicate it with some sanitized data. Thanks

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 10:06:00 GMT

Sorry Stefan, my last answer wasn't correct - it is a link to another table, here's the table view. I still want to count the number of plan objects with this feature though.

Re: Count() behaving as count(distinct)??

swuehl — Tue, 25 Apr 2017 10:37:23 GMT

You would need to count another field, that shows a value (i.e. it is not NULL) for each record where protectionplancodeplusrevision shows a value. Any field in table plan without any NULL will do.

=Count(plan_id)

The reason why you get different results is that Qlik can't really count records for a key field, it's not specified in which table you want to count records. You will get a result, but it can be anything from 'expected' to 'totally off'.

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 10:50:36 GMT

That makes perfect sense. Thanks Stefan!

Re: Count() behaving as count(distinct)??

swuehl — Tue, 25 Apr 2017 10:57:24 GMT

I've just noticed that plan_id is also a key field. Same issue will apply to plan_id.

Need to correct above to

Any non-key field in table plan without any NULL will do.

Maybe plan_processdate (if there are no NULL), or create a new field in that table LOAD in your script, with a constant 1 per record (then you can also Sum(CountField) it).

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 11:21:24 GMT

That's a shame Stefan, I was hoping that wouldn't be the case.

I guess this is a problem with being a 'light' Qlikview user - I'd never have guessed I needed to do that to perform a count! From an SQL perspective the whole point of a primary key is that it's the one field you know is not NULL...

I'd have thought this would be a common issue and there'd be a way to specify counting the key field instances from a specific table rather than having to create a special per-row counter/flag...?

Re: Count() behaving as count(distinct)??

Tue, 25 Apr 2017 11:27:25 GMT

PS: Just temporarily unmarked your answer as correct (no offence intended!) so I'm not discouraging any alternative solutions from being offered..

Re: Count() behaving as count(distinct)??

Fri, 05 May 2017 09:22:08 GMT

Hi Stefan. Any chance you can take a look at where this issue went from our original discussion? I branched thread 1261988 as the counter solution seemed to give inconsistent results and no-one seems to be able to shed any light on what looks to me to be a straightforward issue. Any help would be much appreciated!