Solved ETL - Replace Null Values

HowDoIDomo

Hi,

I have a dataset with null values and text and I am trying to replace them with 0 and 1 respectively. But, when I try to write a formula using the conditions 'If Null' or 'If not null' I keep getting errors.

Does anyone have any ideas?

Thanks!

Edit: I figured out what I was doing wrong.

MarkSnodgrass

There are really a few ways you could do this:

Option 1 - check for nulls

CASE WHEN fieldname IS NULL THEN 0 ELSE 1 END

Option 2 - change nulls to 0 then check for 0

CASE WHEN IFNULL(fieldname,0) = 0 THEN 0 ELSE 1 END

Option 3 - check for text using the length function

CASE WHEN LENGTH(TRIM(fieldname)) >= 1 THEN 1 ELSE 0 END

There are probably more options, but there are a few for you.

MarkSnodgrass

There are really a few ways you could do this:

Option 1 - check for nulls

CASE WHEN fieldname IS NULL THEN 0 ELSE 1 END

Option 2 - change nulls to 0 then check for 0

CASE WHEN IFNULL(fieldname,0) = 0 THEN 0 ELSE 1 END

Option 3 - check for text using the length function

CASE WHEN LENGTH(TRIM(fieldname)) >= 1 THEN 1 ELSE 0 END

There are probably more options, but there are a few for you.

HowDoIDomo

https://community-forums.domo.com/main/discussion/comment/97283#Comment_97283

Thank you @MarkSnodgrass!