topic Re: Excluding nulls, blanks and empty fields in counting rows in QlikView

Excluding nulls, blanks and empty fields in counting rows

Wed, 25 Nov 2020 16:16:04 GMT

I am trying to exclude Null, blanks and empty fields from count but without success. I would like to count rows which has its field 'Description' different from null, blank or empty, so I perform:

=Count({$<[Type] = {'A'}, $(=Len($(=Trim(Description))))={'>0'}>} [Description])

but it is not working. Doing:

=Count({$<[Type] = {'A'}>} [Description]) does not work. It includes empties and blanks.

Also I have tried using Sum function with the same set analysis but without success.

for example, from below table:

Order Type Description

0001 A Order type A

0002 B Order type B

0003 A

0004 A Order type A

The result should be 2. Order 0002 is not counted because is of type B and order 0003 is not counted because its description is empty (blank).

Re: Excluding nulls, blanks and empty fields in counting rows

vishsaggi — Tue, 30 Aug 2016 15:37:31 GMT

Which chart you are using this expression in ?

Re: Excluding nulls, blanks and empty fields in counting rows

Anonymous — Tue, 30 Aug 2016 15:42:21 GMT

the following gives count of Numeric count...(In starigt table or Pivot table)

count(TOTAL( RangeNumericCount( [Description] )))

so may be you can do like this

Aggr (count(TOTAL( RangeNumericCount( [Description] ))),Type)

Re: Excluding nulls, blanks and empty fields in counting rows

Tue, 30 Aug 2016 15:42:26 GMT

I am not using any chart, I only want to show the result in a text object. I expect result to be the number of row of which have field 'Type' set to 'A' and 'Description' field different from blank, empty or null.

Re: Excluding nulls, blanks and empty fields in counting rows

jeevays7 — Tue, 30 Aug 2016 15:45:32 GMT

Hi,

try this,

Count({$<[Type] = {'A'}, Description-={' ','','Null'}>} [Description])

Re: Excluding nulls, blanks and empty fields in counting rows

settu_periasamy — Tue, 30 Aug 2016 15:46:19 GMT

Try this

=Count({$<[Type] = {'A'}, Description={"=Len(Trim(Description))>0"}>} [Description])

Re: Excluding nulls, blanks and empty fields in counting rows

Tue, 30 Aug 2016 16:03:53 GMT

Working! I tried to impove it by doing this:

Count({$<[Type] = {'A'}, $(=Trim(Description))-={'','Null'}>} [Description])

in order to reduce the exclusion list to '' and 'Null' but it was not working, why?

Re: Excluding nulls, blanks and empty fields in counting rows

Tue, 30 Aug 2016 16:04:11 GMT

Your solution also works in case of not nulls! not sure for nulls.... thanks!

Re: Excluding nulls, blanks and empty fields in counting rows

sunny_talwar — Tue, 30 Aug 2016 19:09:01 GMT

Are you saying that if you want to count where description is null? You can try this:

=Count({$<[Type] = {'A'}, Order = {"=Len(Trim(Description)) = 0"}>} [Description])

Re: Excluding nulls, blanks and empty fields in counting rows

Tue, 30 Aug 2016 19:13:47 GMT

No, I want to exclude nulls as well, but I was trying to improve the solution proposed by jeevays7‌I wanted to reduce the exclude list by performing trim over field first but it was not working.

Re: Excluding nulls, blanks and empty fields in counting rows

sunny_talwar — Tue, 30 Aug 2016 19:15:35 GMT

You can try this:

Count({$<[Type] = {'A'}, Description = {"=Len(Trim(Description)) > 0 and Description <> 'Null' "}>} [Description])

Re: Excluding nulls, blanks and empty fields in counting rows

jeevays7 — Wed, 31 Aug 2016 06:11:45 GMT

Hi Tony,

Here,

[Type] = {'A'}, $(=Trim(Description))-={'','Null'}

you cant able to use function in modifier before "=" like this -> $(=Trim(Description))={}.

only use filed names like [Type]=.

then

try this.

Count({$<[Type] = {'A'}, Description-={"$(=Trim(Description)=''))",'Null'}>} [Description])

Try Sunny expressions in last.