Replacing Blank Values in Dataflow

Moberling
Moberling Member
in Magic ETL

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?

Comments

  • GrantSmith
    GrantSmith Coach

    @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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Moberling
    Moberling Member

    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?

     

     

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Godzilla
    Godzilla Contributor

    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'
  • Moberling
    Moberling Member

    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.

Categories