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?

Find more posts tagged with

Comments

GrantSmith

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

MarkSnodgrass

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

jaeW_at_Onyx

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.

Moberling

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?

Set Column Type Error.docx

MarkSnodgrass

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.

Godzilla

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.

Moberling

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.