How can I change a cell that has not data to say "none?"

kim_barragan0126
kim_barragan0126 Member
in Magic ETL

Hello, I have data set that I used the split column in magic ETL. If the column has a blank cell, I want it to say none. Is this possible in ETL or a beast mode? Below is the example, where there is yellow (credit type 2), I want it to say none.

image.png

Best Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    case when `Credit Type 2` is null then 'none' else `Credit Type 2` end

    enter this formula into a formula tile in magic etl. this can also be done in a beastmode.

  • rco
    rco Domo Employee
    Answer ✓

    The empties may be coming into Domo as nulls, but they may also be empty strings (which is different from null). In some cases they could even be strings with "whitespace" characters in them like spaces, though that is only likely if the source of the data is subject to human mistakes.

    Use an Add Formula tile and name the formula Credit Type 2 (the name will become italicized to let you know it is overwriting an existing column). Then write the formula, for handling nulls:

    IFNULL(`Credit Type 2`, 'none')

    Or, to handle both nulls and empty strings:

    IFNULL(NULLIF(`Credit Type 2`, ''), 'none')

    And finally, to handle nulls, empty strings, or blank strings:

    IFNULL(NULLIF(SQUASH_WHITESPACE(`Credit Type 2`), ''), 'none')

    Note that this last formula will also remove any leading or trailing spaces from your non-empty values, and collapse any other repeated spaces into one.

    Randall Oveson <randall.oveson@domo.com>

Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    case when `Credit Type 2` is null then 'none' else `Credit Type 2` end

    enter this formula into a formula tile in magic etl. this can also be done in a beastmode.

  • rco
    rco Domo Employee
    Answer ✓

    The empties may be coming into Domo as nulls, but they may also be empty strings (which is different from null). In some cases they could even be strings with "whitespace" characters in them like spaces, though that is only likely if the source of the data is subject to human mistakes.

    Use an Add Formula tile and name the formula Credit Type 2 (the name will become italicized to let you know it is overwriting an existing column). Then write the formula, for handling nulls:

    IFNULL(`Credit Type 2`, 'none')

    Or, to handle both nulls and empty strings:

    IFNULL(NULLIF(`Credit Type 2`, ''), 'none')

    And finally, to handle nulls, empty strings, or blank strings:

    IFNULL(NULLIF(SQUASH_WHITESPACE(`Credit Type 2`), ''), 'none')

    Note that this last formula will also remove any leading or trailing spaces from your non-empty values, and collapse any other repeated spaces into one.

    Randall Oveson <randall.oveson@domo.com>

  • kim_barragan0126
    kim_barragan0126 Member

    Thank you!

Categories