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

kim_barragan0126

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.

nmizzell

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

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.

nmizzell

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

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.

kim_barragan0126

Thank you!