Case When Blank Value

Hello All

I am trying to capture all Blank values in a certain field. I have tried the following and have not been able to capture the 600K+ Lines that have blank values:

= ’ ’
=’’
= NULL
= ‘-’

Please Help !

Thanks

Hi,

Blank fields are not necessarily NULL, some might actually be filled with STRINGS showing that they are empty. To check whether or not the field is actually NULL, use Celonis offers the ISNULL() function:
ISNULL(field) = 1 is true for all fields that are NULL,
ISNULL(field) = 0 is true for all fields, that contain something, no matter what they contain.
If the values are actually NULL (which Celonis displays as dash), you’re already done, ISNULL(field) = 1 is the way to go.

However, the fields might actually contain a value indicating that they are empty, without being NULL.
For instance, the fields could contain an actual dash, or a whitespace (as you mentioned matching with = ’ ')
In order to find out the correct number of whitespaces you need for your expression you find the “blank values”, create an OLAP table with the following dimension:
’ || field || '
Depending on the result, you now have the expression that is used for blank values in your system:
1. _ _ => 1 whitespace
2. - => an actual dash
3. - => your field is NULL, please use ISNULL()
4. _ _ => your field is filled with 4 whitespaces

Best regards,
Viana