Showing Number of Nulls for Each Field

[#1]

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.

[settu_periasamy]

Maybe try

Nullcount (Field)

Edit:

I didn't test this, but try

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

[sunny_talwar]

May be try something like this:

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

[satishkurra]

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

[#5]

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?

[settu_periasamy · Accepted solution]

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)))

[#7]

This was so helpful! Thank you!

[#8]

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

[sunny_talwar]

Try this:

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

[sunny_talwar]

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])', ',') & ')')