Handle NULL in Magic

JasonAltenburg
JasonAltenburg Coach
edited December 2023 in Magic ETL Ideas

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


Tagged:
6
Up
6 votes

Submitted · Last Updated

Comments

  • TheLookout
    TheLookout Contributor

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

    image.png

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

  • JasonAltenburg
    JasonAltenburg Coach

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

  • TheLookout
    TheLookout Contributor

    @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
    DuncanDomo Contributor

    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:

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


  • JasonAltenburg
    JasonAltenburg Coach

    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.

Categories