Count() behaving as count(distinct)??

[#1]

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?

[mayankraoka]

Hi,

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

Regards,

Mayank

[swuehl]

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?

[#4]

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.

[#5]

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

[#6]

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.

[swuehl]

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'.

[#8]

That makes perfect sense.  Thanks Stefan!

[swuehl · Accepted solution]

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).

[#10]

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...?