Solved ETL - Replace Null Values

HowDoIDomo
HowDoIDomo Contributor
edited July 2024 in Magic ETL

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.

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

Categories