Show Non Null Values by Field

[mjperreault]

Hello Qlik Community!

I am working with a large database table > 100 fields and I have a need to know for each field the number of values that are null and the number of values that are not null.  I tried creating the below loop using FieldValueCount and NoOfRows.

LIB CONNECT TO 'My Database';

My_Table:
LOAD
*;
SQL Select * from mydatabasetable;

Let vNoOfFields = NoOfFields('My_Table'); 


For i = 1 to $(vNoOfFields) step 1

Load
FieldName('$(i)','My_Table') as FieldName,
FieldValueCount(FieldName('$(i)','My_Table')) as NonNullCount,
NoOfRows('My_Table') - FieldValueCount(FieldName('$(i)','My_Table')) as NullCount,
NoOfRows('My_Table') as TotalRowCount
AutoGenerate 1;

Next 

Drop Table My_Table;

This is working with the issue that my NonNullCount is not really a NonNullCount but rather a distinct value count because FieldValueCount() returns distinct FieldValueCount.  I was wondering if anyone has any ideas on how I could return just total FieldValueCount() instead of distinct FieldValueCount.  I figure it has to be possible because Qlik displays this information in the Field Preview section of the data model viewer so this information has to be accessible somehow!

I know I could use a resident and Count() and NullCount() instead of an Autogenerate but this would cause my loop to take hours because of the many fields and many rows.   

Hoping someone might have some thoughts!

Thanks,
Mark

[marcus_sommer]

You could try to do the essentially checks already within the initial (preceeding) load and/or using an appropriate parallel load with something like this for each field:

rangesum(-(len(Field1)>=1), peek('Field1Check')) as Field1Check

which cumulates a NULL check over all records. Afterwards you could within a loop query with peek() the last record from each field to get the number of non NULL values and the difference between this value and noofrows() will be the NULL's.

I doubt that this will be really fast but it should be significantly better performing as the mentioned aggregation-loads.

- Marcus