topic How to create a report on fields with NULL entries in QlikView

How to create a report on fields with NULL entries

Mon, 01 Mar 2010 20:18:58 GMT

I create a pivot table with all the needed fields and do see that some row contains NULL entry on a particular field or column. Now I just want the system to filter for just the NULL entries. The goal is to have a report showing all record with NULL entry in field X.

Any suggestions? Thank you.

How to create a report on fields with NULL entries

Tue, 02 Mar 2010 00:50:28 GMT

i don't think you can do selections on nulls in Qlikview. The way i've solved this is on the load script, i make sure to put some text value on these null ones (i.e. MISSING, EMPTY, '<empty string>', etc. ). This way i can filter by them.

How to create a report on fields with NULL entries

Tue, 02 Mar 2010 15:17:47 GMT

Would you please send me the load syntax you use for filling "empty" or "null" entries. Thank you so much.

How to create a report on fields with NULL entries

Miguel_Angel_Baeyens — Tue, 02 Mar 2010 15:29:28 GMT

Hello,

NULL values can be controlled in a couple of ways. I use

If(Len(Field) = 0, 'Null Field', 'Otherwise')

to control null values. There is a function IsNull() which allows you to know when the value of field is null. It has some erratic behaviour in certain versions and hardware though...

How to create a report on fields with NULL entries

blaise — Tue, 02 Mar 2010 15:52:00 GMT


if(isnull(Field),'null',Field) as Field

Will replace all null values with 'null' for the Field. There is other functions (something like nullinterpret etc) that might do the trick but this is the one i use. You can also use this function in your pivot table expressions.