Handle NULL in Magic

JasonAltenburg · 2022-01-13T20:22:33+00:00

There was an error rendering this rich post.

It would be nice to have a tile, or an option in Alter or Join tiles to mass handle NULL values.

I understand that I can use a formula for every column as follows but after about 3 columns it gets tedious.

case 
when `col1` is NULL then ''
else `col1`
end

Find more posts tagged with

Implemented

Status: Implemented

Comments

TheLookout

If the NULL values exist in the input dataset, you can configure that fields settings in the "Input DataSet" tile.

Not as easy as a tile to handle it for all fields at once but it is easier than a formula tile.

JasonAltenburg

@TheLookout That's awesome! Thank you, this will save me a bunch of time.

TheLookout

@JasonAltenburg I wanted to add one thing, if you're doing this later in the dataflow you can use the IFNULL() command rather than a case statement. IFNULL(`col1`,'') will return the same value as your example case statement. I use that in combination with the NULLIF() command frequently. IFNULL(NULLIF(`col1`,''),`col2`) If column 1 is blank or null, it will be replaced by column 2. If column 1 is not blank or null, it will remain its current value. A little more concise then writing out a full case statement or coalesce.

DuncanDomo

Could we also add a feature to this to mass handle NULL OR blank strings (even if one space)

I always end up writing something like:

case
when `col1` is NULL or Len(trim(`col1`) <1 then ''
else `col1`
end

JasonAltenburg

I just ran into this problem again, I appreciate your feedback @DuncanDomo that's exactly the sort of tile I'd like to see. Creating another dataflow and chaining them just to handle nulls is a bit problematic and looks bad / hard to teach for end users.