Convert null numeric values to '0' in Magic ETL

cameronhobbs

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

mdelorey

@cameronhobbs: I was working on some DataFlows today and saw your comment here so I took it as a challenge. The ideal would have been to use one input dataset, send it through a value mapper action that searches for null values in a column and replaces with 0, and then output one dataset. I tried to configure that, but you can't search for null values in a Value Mapper action (at least not on a column that is a whole number type - the Value Mapper step outputs this error if you try: Unexpected conversion error while converting value [string String] to type Integer.).

 

So.. here's a screenshot of what I did as a workaround that got it to work:

 

Step 1: Filter your column's null values into a separate branch using a Filter Row action.

Step 2: Add an Add Constants action to the null branch that adds a new column with whatever name (Zero Value is what I used) with a value set to 0.

Step 3: Add a Select Columns action to the null branch and select the columns you need except the column that holds the null values (the column you set the filter on in Step 1).

Step 4: Left join your filtered branch with your input dataset on your unique key.

Step 5: Combine the original column on which you set the filter in Step 1 with the constant column you added to the null rows in Step 2 usins a Combine Columns action.

 

Here's a link to the input dataset I used (Google sheet): http://bit.ly/null-dataflow-helper

 

Here's a screenshot of my input and output datasets:

 

Let me know what you think! I'm happy to post follow up screenshots of any of my action configurations if that would help.

 

mdelorey

@cameronhobbs: I was working on some DataFlows today and saw your comment here so I took it as a challenge. The ideal would have been to use one input dataset, send it through a value mapper action that searches for null values in a column and replaces with 0, and then output one dataset. I tried to configure that, but you can't search for null values in a Value Mapper action (at least not on a column that is a whole number type - the Value Mapper step outputs this error if you try: Unexpected conversion error while converting value [string String] to type Integer.).

 

So.. here's a screenshot of what I did as a workaround that got it to work:

 

Step 1: Filter your column's null values into a separate branch using a Filter Row action.

Step 2: Add an Add Constants action to the null branch that adds a new column with whatever name (Zero Value is what I used) with a value set to 0.

Step 3: Add a Select Columns action to the null branch and select the columns you need except the column that holds the null values (the column you set the filter on in Step 1).

Step 4: Left join your filtered branch with your input dataset on your unique key.

Step 5: Combine the original column on which you set the filter in Step 1 with the constant column you added to the null rows in Step 2 usins a Combine Columns action.

 

Here's a link to the input dataset I used (Google sheet): http://bit.ly/null-dataflow-helper

 

Here's a screenshot of my input and output datasets:

 

Let me know what you think! I'm happy to post follow up screenshots of any of my action configurations if that would help.

 

cameronhobbs

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

mdelorey

@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.

canioacaputo

Great solution.  well done

John-Peddle

@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

@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!

 

 

 

JSensei

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.

John-Peddle

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

bankuda

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

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

AMousavi

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.

jaeW_at_Onyx

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.

https://www.w3schools.com/sql/func_mysql_coalesce.asp

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!