Show Non Null Values by Field

mjperreault — Thu, 10 Jun 2021 14:34:03 GMT

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

Re: Show Non Null Values by Field

marcus_sommer — Thu, 10 Jun 2021 16:40:52 GMT

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

topic Re: Show Non Null Values by Field in App Development

Show Non Null Values by Field

Re: Show Non Null Values by Field