Count Where Field Value is Null Not Working

[aointl_jelee]

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? 

[aointl_jelee]

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.

[antoniotiman]

Hi,

try this

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

However this may not work depending on Your Data Model.

Regards,

Antonio

[aointl_jelee]

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

[vishsaggi · Accepted solution]

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

OR

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

Then you can use Count() Like

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

[aointl_jelee]

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

[vishsaggi]

Sounds good. Glad it helped you.