Handle NULL in Magic

Options
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


6
6 votes

Implemented · Last Updated

Good news - a suite of tiles that allows you to do operations like this is in beta now and will be available in everyone's instances with the May Customer Release. They're called Schema and Meta Select

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