Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 

Count Blanks in Every Field in Script via Loop


Basically I have a very large/incomplete dataset and want to know where my blanks/nulls are.

To count my blanks in one column I am using If(Len(Trim([Field])) = 0, 1, 0) as NullFlag. 

My issue is that I have 345 fields and wanted to know if there was a way I can use a loop of some sort rather than having to type this out 345 times? 

Labels (5)
3 Replies
Champion III
Champion III



my issue isn't with the formula itself, that works. I am able to count the blanks. My issue is with that I want to know if there is a fast way to format it where I do not have to type 345 lines of code and can just loop through all my fields applying the same calculation to each. 

MVP & Luminary
MVP & Luminary

I think it depends on your real requirements (the aim behind your question) how expensive your task may be. This means what do you really want to know / to count - NULL's against what? The reason for it is that each field in Qlik is unique regardless how many times it was loaded respectively in how many tables it occur.

If it should really against each table you would need to apply such logic after each load or within the UI you would need a certain identifier-field for each table on which you could relate within your count-expression.

If I had such a task - of detecting and identifying issues with the data-quality - I wouldn't start with it in the script else in the UI by creating a pivot with $Table and $Field as dimensions and fieldvaluecount($Field) and sum($Rows) as expressions. The difference of both might then be your NULL's - whereby this is usually only part of checking data and often not the most interesting one because you might also have a lot of wrong and/or unexpected data.

To see the possible content of a field you could use two listbox - one with $Field and one with the expression: =$(='['&GetFieldSelections($Field)&']') and for any relations between your fields you could use a few tableboxes (if no unique id's are available for this kind of view you may apply some rowno() and/or recno() within your loads). 

- Marcus