Catching dashes (-)

Hi all,

I have some issues finding null values. The column “EISBE” in the Marc table returns a numeric value. All values greater than 0 are classified as MTS. Values that are 0 are classified as MTO. However, this column has many null entries (around 70-80%) that I would like to consider as MTO as well. My code:

CASE WHEN “MARC”.“EISBE” > 0 THEN ‘MTS Material > 0’
WHEN “MARC”.“EISBE” = 0 THEN ‘MTO Material because 0’
WHEN ISNULL(“MARC”.“EISBE”) = 1 THEN ‘MTO Material because null’
WHEN “MARC”.“EISBE” IS NULL THEN ‘IS NULL’
END

In the end, the result consists of these values: ‘MTS Material > 0’, ‘MTO Material because 0’ and ‘-’. How can i catch these dashes?

Thanks in advance

Hi,

the CASE WHEN returns NULL when a condition is NULL. Your first condition would therefore evaluate to NULL, and NULL is returned.
So the solution would be to check for NULL at the beginning of your CASE WHEN, then the corresponding string will be returned:

CASE WHEN ISNULL("MARC"."EISBE") = 1 THEN 'MTO Material because null'
WHEN "MARC"."EISBE" > 0 THEN 'MTS Material > 0'
WHEN "MARC"."EISBE" = 0 THEN 'MTO Material because 0'
END

In your query, the last and the second to last conditions are the same, so I left out the last condition here.

Best
David

Hi David,

Thank you for your reply. Unfortunately, your suggestion did not have a better outcome. It didn’t change anything… Any other ideas?

Do you have other dimensions in your OLAP table? Maybe the NULL values come from missing join partners. You can check that by deactivating all other dimensions of your OLAP table and check if you still have NULL values.
If the NULL values come from the missing join partners, you need to introduce the join inside the CASE WHEN. This post might help you: Unable to make NULL Value as the selector in an OLAP Field

Let me know if that solves your problem, or if you need further information on this.

Best
David