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

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

awhitfield — Tue, 25 Apr 2017 11:38:35 GMT

I'd go with adding a specific counter field in the load script, simple and sensible

Andy

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

Thu, 04 May 2017 13:55:52 GMT

I implemented counters as Andrew suggested, but have some weird behavior as a result. Please can anyone explain?

The data structure is:

I have a simple chart counting consumers by territorysector entry:

and the result I get is:

It appears that the same consumer is being counted twice because they have 2 Plan entries, but the whole point of using the 'counter' field was that it would count distinct Consumer table entries only. Why does this not work??

Thanks for any help you can give

Graham

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

m_woolf — Thu, 04 May 2017 13:59:52 GMT

why not:

count(distinct consumer_id)

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

PrashantSangle — Thu, 04 May 2017 14:06:40 GMT

Hi,

not sure but if you want result of count() match with count(distinct ) you have to normalize your data model.

Regards,

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

vinieme12 — Thu, 04 May 2017 14:16:22 GMT

you should be using Count(Distinct if you don't want to use Count(Distinct .(which is actually faster than count!)

create a separate table as below

Load Distinct Customer_ID, 1 as Customer_Counter

from Resident Consumer

Then change your expression to Sum(Counter) or count(Counter)

A Myth About Count(distinct …)

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

Thu, 04 May 2017 14:17:55 GMT

Sorry, the original thread is https://community.qlik.com/message/1256371?et=watches.email.thread#1256371

I branched off this discussion rather than reopening that thread, but if you check it out you can see that I was originally using count() with a key field as you suggest, and getting unpredictable results (counts of 1 for everything).

I was recommended to add a counter field (with content = '1') to tables I wanted to count instead to ensure I was counting from the correct table. I understood that was because counting a key field can give a result according to its presence in a linked table, rather than its 'primary' table?

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

Thu, 04 May 2017 14:36:59 GMT

Hi Vineeth. Sorry, I don't understand. Why would a separate table linked 1 to 1 to the consumer table behave any differently to adding a counter field to the consumer table itself?

This branch comes off thread 1256371 (I included a link in an earlier post but that's "being moderated" - I presume because it's a link...)

In the original post I was asking about why when I applied a count() to a key field I got a count of 1 for everything.

I was recommended to add a counter field (with content = '1') to tables I wanted to count instead, to ensure I was counting from the correct table. I understood that was because counting a key field can give a result according to its presence in a linked table, rather than its 'primary' table?

I presume DISTINCT won't help in a case where I'm getting 1 for everything...?

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

vinieme12 — Thu, 04 May 2017 14:46:00 GMT

Because the second table will only hold Distinct Values!

If it's difficult to understand try the below sample

fact:

Load Person,1 as PersonCounter inline [

Person

e];

DistinctPerson:

Load * Distinct Person,1 as Counter

Resident fact;

Now Sum(Counter) or count(Counter) is unique count of people

and Sum(PersonCounter) or Count(PersonCounter) is the count of instances the person occurs in Fact

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

Thu, 04 May 2017 14:52:40 GMT

Hi Vineeth. I should have pointed out - my Consumer table already contains distinct values - my database is fully normalized

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

swuehl — Fri, 05 May 2017 10:15:30 GMT

How do you create the counter field? Could you post your script?

You should get a count of 1 if your consumer table does only show a record per consumer id.

Could you post your QVW? Maybe with scrambled data, if needed:

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

But after all, if you are really interested in the number of distinct consumers, I would suggest using

Count(DISTINCT consumer_id)

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

Fri, 05 May 2017 10:47:24 GMT

Sorry Stefan/everybody - I finally spotted the problem. I was convinced there was no chance of this, but some duplicates had got into the consumer table as part of the data preparation process..

Great news really though - gives me confidence that the counter method will work consistently once I've resolved the duplication!

Thanks again for your assistance in resolving this.

Graham