Count() behaving as count(distinct)??

[awhitfield]

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

Andy

[#2]

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

[m_woolf]

why not:

count(distinct consumer_id)

[PrashantSangle]

Hi,

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

Regards,

[vinieme12]

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

[#6]

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?

[#7]

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

[vinieme12]

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

A

A

A

B

B

c

c

c

d

d

e

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

[#9]

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

[swuehl]

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)