Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
aointl_jelee
Contributor
Contributor

Count Where Field Value is Null Not Working

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? 

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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)

View solution in original post

6 Replies
aointl_jelee
Contributor
Contributor
Author

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
Master III
Master III

Hi,

try this

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

However this may not work depending on Your Data Model.

Regards,

Antonio

aointl_jelee
Contributor
Contributor
Author

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
Champion III
Champion III

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
Contributor
Contributor
Author

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
Champion III
Champion III

Sounds good. Glad it helped you.