Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
Maybe try
Nullcount (Field)
Edit:
I didn't test this, but try
Nullcount($(=$Field)) in pivot table expression
May be try something like this:
Max(TOTAL [$Rows]) - [$Rows]
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
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?
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)))
This was so helpful! Thank you!
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
Try this:
$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',Sum({<RecordNumber = {"=NullCount([' & $Field & ']) > 0"}>}' & '[Amount])', ',') & concat(right($Field&')',1)))
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])', ',') & ')')