Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'd go with adding a specific counter field in the load script, simple and sensible
Andy
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
why not:
count(distinct consumer_id)
Hi,
not sure but if you want result of count() match with count(distinct ) you have to normalize your data model.
Regards,
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 …)
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?
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...?
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
Hi Vineeth. I should have pointed out - my Consumer table already contains distinct values - my database is fully normalized
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
But after all, if you are really interested in the number of distinct consumers, I would suggest using
Count(DISTINCT consumer_id)