Convert null numeric values to '0' in Magic ETL

Trying to convert null values in an numeric column in an Excel spreadsheet in order to perform calculations on that column. Currently unable to find a work around

Best Answer

Answers

  • @mdelorey thanks for the input! I appreciate you taking the time on this, its definitely a viable soution. Smiley Happy

  • mdelorey
    mdelorey Domo Employee

    @cameronhobbs Glad I was able to help! Like I said, feel free to reach out to me if I can get you any other info. I'm anxious to hear if it works for you.

    Retired Domo Alum
  • Great solution.  well done

  • John-Peddle
    John-Peddle Contributor

    @mdelorey - I know it's been a couple years since this solution was provided but wondering if Domo alllows you to filter rows/set constants/combine columns for multiple instances in a single dataset? 

     

    We have 12-15 columns that have null values that we'd like to replace the w/0's; I'm trying to repeat step 5 (add a new column combine tile) for each new column after step 4 (left joining the dataset but it's not letting me connect the tiles.

     

    Thanks!

  • John-Peddle
    John-Peddle Contributor

    @mdelorey - had to play with it a bit more to find out WHERE you can add them, but yes, it does allow for multiple instances of joining columns to replace null values. Thanks for the post, this has proven to be very helpful!

     

    Screen Shot 2018-08-16 at 10.50.35 PM.png

     

     

  • Sorry my reply won't have a graphic. Fastest way to do it for multiple rows is to use "set column type" to convert all numeric columns to text, use the "replace text" to convert nulls to zeros, & then "set column type" again to convert back into numeric types. 3 steps no matter how many columns you're replacing values for.

  • Thanks @JSensei, appreciate the followup on this! Have a great day...  Smiley Happy

  • Thanks @mdelorey and @JSensei for your responses. I've used @JSensei 's method a few times now. Would there be better performance using one method or the other for achieving the desired results?

  • HeatherDomo
    HeatherDomo Domo Employee

    This is an old thread - BUT - Magic ETL2 has more efficient ways of handling null -

    on input click on data handling, then the settings to set null values to Nil (0)

    Alternatively, if nulls are generated in the flow use the Alter Columns tile - select columns to handle Null also as Nil


  • Thanks @HeatherDomo ! Your solution is the best and I will help me to get rid of the unnecessary tiles in my ETL's. Thanks a lot.

  • for the original use case using a formula tile and COALESCE() will be a much cleaner (and recycle-able) design pattern in other parts of the platform.


    I particularly enjoy the fact that COALESCE is a SQL standard and not bespoke to MAGIC ETL.

    My challenge with setting data types in the INPUT dataset is last time i looked at the feature, Domo did not have particularly strong support for error handling.

    The formula tile gives you the most granular control in one tile over CAST() ing data types as well as creating logic for errors. -- No proper TRY / CATCH blocks... but better than nothing!

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"