Replacing Blank Values in Dataflow

Reply
Highlighted
Yellow Belt

Replacing Blank Values in Dataflow

I am creating an ETL dataflow in which I need to join my input dataset with other data sources via my column 'Sold-to Industry Code 1'.  Although the values in 'Sold-to Industry Code 1' are numbers, Domo is treating it as text.  This column is brought into Domo with blanks.  I am trying to convert 'Sold-to Industry Code 1' to a whole number field via 'Set Column Type' to perform my join.  It appears I cannot perform this action due to the blank values in 'Sold-to Industry Code 1' in some rows.  I have attempted to replace my blank values via the 'Replace Text' funtion but to no avail.  How do I both replace blank values in my column and convert the resulting column to a whole number?

Highlighted
Red Belt

@Moberling 

 

Domo should handle empty strings correctly when converting to a whole number using the Set Column Type tile (It converts them to NULL values).

 

Do you have a screenshot of your ETL or some sample data? Is there a specific error you're getting? How is it not working correctly?



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Black Belt

@Moberling I wonder if there are spaces in that field rather than it actually being empty and that is why it can't convert. Try bringing in a String Operations tile and use the trim spaces function to eliminate spaces. Then try the Set Column Type. 

You could also use the Combine Columns tile to try and expose what is in that column. In this tile, for the part that says "what character should separate, choose None. Then for which columns to add, pick two columns that always have data in them and have your problem column be the middle column of the 3 columns. This should help you see if there is anything there or not.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Black Belt

Testing for blanks inETL can be such a PITA!

 

Maybe start with a Filter and try a couple of Filter permutations until you've locked down exactly what the data contains.  You can try to use the length function to see if the blank is truly blank or space.

 


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Yellow Belt

Hi,

Thank you ever so much for your input and my apologies on my delayed response.  Attached is the image that shows the format of the column I am attempting to manipulate. I was successful in utilizing the 'String Operations' to trim spaces from this column and then convert to a whole number and then successfully perform my downstream joins.  However, in doing so, it appears I have now omitted all rows in which the value of 'Sold-to Industry Code 1' is null (see attached document).

 

So - I am still unable to convert my 'Sold-to Industry Code 1' text column to a number while maintaining all row data for null values in this column.

 

Any suggestions from here?

 

 

Highlighted
Black Belt

I see in your attachment in the first screenshot that the dataset contains 61,598 rows. What is the row count of the dataset in your last screenshot where you are filtering on nulls and there are no rows for that filter? Is it the same? If so, then you didn't lose any rows.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Major Brown Belt

Are you using the 'Sold-to Industry Code 1' field to join? is it possible you're join is causing the nulls to fall out? make sure you're doing an outer join. 

 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Highlighted
Yellow Belt

I was unable to successfully make changes in the dataflow to my source data.  My solution was:

change the column format of my flat lookup file to match the format of 'Sold-to Industry Code'.  My join worked once formats matched.   I then addressed nulls via a beastmode 'ifnull' function.

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.