topic Showing Number of Nulls for Each Field in QlikView

Showing Number of Nulls for Each Field

Fri, 08 Apr 2016 15:07:12 GMT

I would like to create a table that lists every field, which has columns for the total number of nulls, the percentage of rows that have a null, etc. What is the best way to go about this? I have a pivot table that uses $Field as the dimension, but I am unsure how to count the null values in Qlikview.

Re: Showing Number of Nulls for Each Field

settu_periasamy — Fri, 08 Apr 2016 16:48:25 GMT

Maybe try

Nullcount (Field)

Edit:

I didn't test this, but try

Nullcount($(=$Field)) in pivot table expression

Re: Showing Number of Nulls for Each Field

sunny_talwar — Fri, 08 Apr 2016 16:53:26 GMT

May be try something like this:

Max(TOTAL [$Rows]) - [$Rows]

Re: Showing Number of Nulls for Each Field

satishkurra — Fri, 08 Apr 2016 16:58:38 GMT

MAy be this

1. Create a field called system field $Field on to the UI

2. List box with expression as =[$(=minstring($Field))]

3. Label as =minstring($Field)

4. Create Pivot Table with no dimension and expressions as

sum(if(isnull( [$(=minstring($Field))]), 1, 0))

Label as Null Values

Re: Showing Number of Nulls for Each Field

Mon, 11 Apr 2016 14:11:02 GMT

Thanks for the help so far! I've tried to implement the first two suggestions, but I'm still having trouble. I've created a sample QVW that I'm hoping one of you can help with.

The Type field of this file had 58 nulls. Ideally, I would like something to result like this:

Field Name Total Number of Rows Total Number of Nulls % of Null Rows

Amount 246 0 0

ID 246 0 0

Rec Number 246 0 0

Type 246 58 .2357

Year 246 0 0

What is the expression needed to calculate the total number of nulls?

Re: Showing Number of Nulls for Each Field

settu_periasamy — Mon, 11 Apr 2016 15:51:12 GMT

Attached good app, Got it from here... Chart with count of $Field values

$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',nullcount([' & $Field & '])', ',') & concat(right($Field&')',1)))

Re: Showing Number of Nulls for Each Field

Mon, 11 Apr 2016 16:58:00 GMT

This was so helpful! Thank you!

Re: Showing Number of Nulls for Each Field

Mon, 11 Apr 2016 17:27:58 GMT

To take this one step further, how would I create a field to find the total amount associated with the nulls?

Field Name Total Number of Rows Total Number of Nulls % of Null Rows Total Null Amount

Amount 246 0 0 0

ID 246 0 0 0

Rec Number 246 0 0 0

Type 246 58 .2357 $26,402

Year 246 0 0 0

Re: Showing Number of Nulls for Each Field

sunny_talwar — Mon, 11 Apr 2016 17:58:40 GMT

Try this:

$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',Sum({<RecordNumber = {"=NullCount([' & $Field & ']) > 0"}>}' & '[Amount])', ',') & concat(right($Field&')',1)))

Re: Showing Number of Nulls for Each Field

sunny_talwar — Mon, 11 Apr 2016 18:10:37 GMT

This might give you a slightly better performance with large number of data:

$(='Pick(Match($Field, ' & Concat(Chr(39) & $Field & Chr(39), ',') & '),' & Concat('Sum({<RecordNumber = {"=NullCount([' & $Field & ']) > 0"}>}[Amount])', ',') & ')')

Re: Showing Number of Nulls for Each Field

hectorgarcia — Wed, 06 May 2020 13:32:37 GMT

thanks Settu for your contribution with the field observer.

Is it possible to generate the same table but in the script? to create data qulaity qvd's with this content?

Re: Showing Number of Nulls for Each Field

mehul_qlik — Tue, 17 Jun 2025 18:44:56 GMT

Hi settu_periasamy,

I am getting 0 as null value count while using above expressions . Can you please help me

Re: Showing Number of Nulls for Each Field

mehul_qlik — Sat, 21 Jun 2025 10:30:14 GMT

Hi Sunny,

I am getting 0 after using you expression. Can you please help me on this .

Re: Showing Number of Nulls for Each Field

gq — Mon, 23 Jun 2025 13:23:28 GMT

To count the Nulls, either you can use Isnull function or length function.

I usually use Len(Trim(Field)) = 0 to check if the value of the field is null, Trim is used to make sure it does not count any spaces or blanks.

To count the nulls in the field, can use if condition in combination with Len Function.

Try below
Sum(distinct IF(Len(Trim(Field)) = 0, 1, 0))

Re: Showing Number of Nulls for Each Field

mehul_qlik — Wed, 25 Jun 2025 05:40:57 GMT

Thanks ! I tried but still it giving me 0 as results.