topic Re: Count Problem in QlikView

Count Problem

Wed, 03 Sep 2014 14:20:50 GMT

Hi,

I'm facing a problem that I know is due to the disconnect between how I think QV stores data and how it actually does.

My data has a Unique ID and a Name field. The data has 100,00 rows.

I can see in Excel that a Unique ID exists and a Name exists for every row.

If I use Count (Name) I get 45,000. This is because many people share the same name.

Count(DISTINCT (Name) also gives me 45,000.

What expression can I use to give me the result 100,000. I am trying to identify the level of completeness in this and other fields.

For example when I move on to eMail I may only have 20,000 rows with an entry and roughly 19,800 distinct emails. In this example I would want 20,000 as my result.

Hopefully this makes sense - any help appreciated

Thanks

Stuart

Re: Count Problem

MK_QSL — Wed, 03 Sep 2014 14:25:14 GMT

Try

COUNT(ALL Name)

and

COUNT(ALL Email)

Re: Count Problem

Wed, 03 Sep 2014 14:28:14 GMT

Thanks for the quick reply Manish

When I do Count (ALL Name) I get the same results as Count (Name). 45,000 instead of 100,00

Re: Count Problem

vidyut — Wed, 03 Sep 2014 14:37:06 GMT

Count( Name&UniquID) -- Does that work?

Re: Count Problem

Anonymous — Wed, 03 Sep 2014 14:52:16 GMT

Create a Statistic Boxes for your Unique ID and Name field respectively and add all the 'count' functions to be displayed.

From that you should be able to see what';s what, especially regarding Missing and Null values. If the answer is not obvious then please post the results.

Re: Count Problem

Wed, 03 Sep 2014 15:27:30 GMT

Thanks but no it doesnt. I always get a count of 100,000 for that as there is a Unique ID for everything and that probably concatanates the fields.

Re: Count Problem

Wed, 03 Sep 2014 15:28:33 GMT

Thanks for the hint on adding a stats box. This is very usefull.

Do stats boxes not work with Key fields as for my Name field I get n/a for everything. But my Email field I get proper results.

Re: Count Problem

Anonymous — Wed, 03 Sep 2014 15:39:06 GMT

Aaahh !!

So your Name field is a Key, as in a field in 2 different table that joins them. I reckon that could well be your joker.

Could you share a screenshot of your data model and load script ?

Re: Count Problem

preminqlik — Wed, 03 Sep 2014 15:40:43 GMT

if i understood, try this

Count(distinct uniqueID)

or else

while loading the data take the rowno() parameter like below : because according to AQL logic duplicated rows are removed

Load *,

rowno() as rowno,

recno() as recno

from path;

see this also

count(distinct rowno) for validating

Re: Count Problem

Wed, 03 Sep 2014 16:00:35 GMT

yes it was the key that was causing the issue.

I brought the field in a second time just for counting purposes and it now works.

thanks

Stuart