topic Re: Count Where Field Value is Null Not Working in QlikView

Count Where Field Value is Null Not Working

aointl_jelee — Tue, 13 Jun 2017 14:33:04 GMT

Hi Community,

Currently developing a matrix report to check values from various tables to determine if a value is present.

I am using the methodology to count where field values are null by using set analysis.

Example: COUNT({$<[FIELD]-={''}>}DISTINCT ([DIMENSION FIELD]))

This works fine when the FIELD contains at least one value (even if it is not associated). When the FIELD does not contain any value, this does not work. I receive just a distinct count of the dimension field.

What am I doing wrong here?

Re: Count Where Field Value is Null Not Working

aointl_jelee — Tue, 13 Jun 2017 14:53:55 GMT

To be clear, I am dealing with Missing Values.. When there is no record for the table which has the field I am checking for, I am returned with a distinct count of the dimension field.

Re: Count Where Field Value is Null Not Working

antoniotiman — Tue, 13 Jun 2017 16:03:07 GMT

Hi,

try this

Count({<DIMENSION={"=Len(Trim(FIELD))=0"}>} DIMENSION)

However this may not work depending on Your Data Model.

Regards,

Antonio

Re: Count Where Field Value is Null Not Working

aointl_jelee — Tue, 13 Jun 2017 17:00:40 GMT

Antonio,

Thank you for your response. I am familiar with this method of checking if a field is null or not null as well.

However, like you suggested, my data model does not have any possible value for this field, yet. These are new columns added to the database table, which have yet to be populated.

-Josh

Re: Count Where Field Value is Null Not Working

vishsaggi — Tue, 13 Jun 2017 20:27:34 GMT

Why don't you use a flag in your script like: I am not sure if this works just try and let us know.

IF(ISNULL(FieldName) = -1, 0, 1) AS FieldValueFlag

IF(Len(Trim(FieldName)) = 0, 0, 1) AS FieldValueFlag

Then you can use Count() Like

Count({<FieldValueFlag = {0} >}FieldName)

Re: Count Where Field Value is Null Not Working

aointl_jelee — Tue, 13 Jun 2017 20:38:28 GMT

Vishwarath,

That is exactly what I did this afternoon. Thank you for the correct answer.

I ended up having to prepopulate the transactional table with my key field, then left join the transactional data back to this table so that I would have the ability to create the flags with proper association.

Many thanks,

-Josh

Re: Count Where Field Value is Null Not Working

vishsaggi — Tue, 13 Jun 2017 21:05:09 GMT

Sounds good. Glad it helped you.