topic Re: Return count where field is blank in QlikView

Return count where field is blank

Fri, 06 Jun 2014 15:46:04 GMT

I need to do a unique count of UserNames where the Firm field is blank

I tried this, but it didn't work

=COUNT(DISTINCT {$<Firm-={'*'}>}UserName)

any ideas?

Re: Return count where field is blank

venkatg6759 — Fri, 06 Jun 2014 15:50:32 GMT

=Count({$<Firm={' '}>}Distinct UserName)

Re: Return count where field is blank

Fri, 06 Jun 2014 15:54:36 GMT

NullCount([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression)

Returns the aggregated count of NULL values from expression or field iterated over the chart dimension(s).

You might try the nullcount function. The above was taken from QlikView help file.

Re: Return count where field is blank

Fri, 06 Jun 2014 16:02:29 GMT

This didn't work. It returned 0

Re: Return count where field is blank

Fri, 06 Jun 2014 16:06:10 GMT

How are your nulls formatted?

Re: Return count where field is blank

Fri, 06 Jun 2014 16:11:05 GMT

It's a text field, which is totally blank

Re: Return count where field is blank

Fri, 06 Jun 2014 16:26:29 GMT

Have you tried =count(distinct {$<Firm = {'',' ',null}>} UserName)

Re: Return count where field is blank

Fri, 06 Jun 2014 16:33:54 GMT

I think my problem may be solved another way

The root issue is that this expression does not count UserNames where the Firm field is blank

=COUNT(DISTINCT {$<Firm-={'ACME','*TEST*'}>}UserName)

Is there a way to alter this to ensure it includes UserName where the Firm field is blank, whilst also excluding Firms called ACME or with TEST in them?

Re: Return count where field is blank

Fri, 06 Jun 2014 16:38:33 GMT

Maybe in your load script, use something like if(Firm = ' ', null, Firm) as Firm? so then all blank values are associated with nulls, then you can use nullcount?

Re: Return count where field is blank

CELAMBARASAN — Fri, 06 Jun 2014 16:44:33 GMT

Something like this

=COUNT(DISTINCT {$-<Firm={'*'}>}UserName)

Re: Return count where field is blank

rwunderlich — Fri, 06 Jun 2014 16:46:13 GMT

I think this will handle both blanks and nulls:

COUNT(DISTINCT {$<UserName={"=len(trim(Firm))=0"}>}UserName)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Return count where field is blank

Fri, 06 Jun 2014 16:49:32 GMT

Thanks Jacob. Would I have to adapt my expression? Or would the null values be counted automatically?

Re: Return count where field is blank

Fri, 06 Jun 2014 16:51:41 GMT

I think you could use NullCount(distinct UserName), but you could also try Rob's solutions. They might be simpler.