This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hello all, I have a flatfile that contains transactional data. I m currently facing issues with the conversion of metrics from strings to numbers. The values that i am trying to convert are sales and units because they contain text that i would like to get rid of before i convert them to numbers (i.e. sales are displayed as '100 EUR', and units are displayed as '2 PCE' ; i would like these to be displayed as '100' and '2'). I am first using the formula tool to replace 'EUR' and 'PCE' to ' ' (i.e. Blank), then using the select tool to convert from 'V-string' to 'double'. I am however noticing that as i am running the workflow a lot of rows get suppressed (error message type : ' 30 EUR lost information in translation ' ) and am thus facing data completeness issues with my extract. Could someone help in making sure that all fields get properly converted and kept in the final extract ? Many thanks, Kamel
What about taking a Multi-Field tool approach, to strip everything that is not a number from your field.
Here, I'm using Regex_Replace to look at both columns, and replace any non-number value with nothing. I've created 2 new fields to store the numbers, and there were no conversion errors. I love this approach because you can assign a data type to the new fields (and even change data type of an existing field!), and apply the same formula to both columns.