Lost information in translation

Question

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

LukeM · Accepted Answer

Hi @kamel1002

I would look at dealing with this more dynamically as your Replace() function may be missing some of your text values.

Are the values all in the format number-space-currency? If so, you could use a text to columns to split the numeric and the currency into different fields.

Alternatively, you could leverage the RegEx parse tool or RegEx functions in the Formula tool to remove any text characters:

* Use formula tool to create new numeric field
* Leverage REGEX_Replace([Field],'[A-Z\s]','') to remove all letters and space characters.

Hope this helps. Let me know if you have any more questions.

Luke

estherb47 · Accepted Answer

Hi @kamel1002

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.

Let me know if that helps.

Cheers!

Esther