Convert null numeric values to '0' in Magic ETL

Reply
White Belt

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


Accepted Solutions
Retired

Re: Convert null numeric values to '0' in Magic ETL

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

screenshot_456.png

 

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:

screenshot_458.jpg

 

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

 

Retired Domo Alum

All Replies
Retired

Re: Convert null numeric values to '0' in Magic ETL

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

screenshot_456.png

 

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:

screenshot_458.jpg

 

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

 

Retired Domo Alum
White Belt

Re: Convert null numeric values to '0' in Magic ETL

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

Retired

Re: Convert null numeric values to '0' in Magic ETL

@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
Yellow Belt

Re: Convert null numeric values to '0' in Magic ETL

Great solution.  well done

Green Belt

Re: Convert null numeric values to '0' in Magic ETL

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

Green Belt

Re: Convert null numeric values to '0' in Magic ETL

@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

 

 

Visitor

Re: Convert null numeric values to '0' in Magic ETL

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.

Green Belt

Re: Convert null numeric values to '0' in Magic ETL

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

Visitor

Re: Convert null numeric values to '0' in Magic ETL

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?

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!