Clean up Double Field

[ZoeM]

Hi!

All right, I have a double field that is throwing two errors

1. "-" is not a valid fixed decimal...

2. Field Conversion error limit reached

How can I clean up the field and remove the dash '-', I have tried RegEX and straight If function but none seem to work.

And how can I figure out the conversion error limitation?

Thanks gurus!

[ShankerV]

Hi @ZoeM 

Usually - is considered an negative value and it is allowed.

Could you please share sample where you encountering this issue.

Many thanks

Shanker V

[binuacs]

@ZoeM

toNumber( Replace([Field],'-',''))

[JamesCharnley]

Hi @ZoeM 

Are you able to suggest screenshots at all? If you just need to remove all of your '-' which sounds like it might be the case, then a simple Replace or ReplaceChar function should work.

Something like Replace([yourfieldhere], '-','') 

where your third parameter has nothing in quotes, essentially saying taking every instance of a - and replace it with nothing.

Edit: I for some reason was imagining these '-' to be in the middle of numbers as separators and not negative values. I'm now assuming they're just negative values but are you able to confirm which it is?

[ZoeM]

Screen shot attached. Id assume it was from the source?

I tried the replace and it was throwing an error, I assume because this is a Double field?

[binuacs]

@ZoeM Can you provide a sample amount field? 

[ZoeM]

Attached a similar workflow

[binuacs · Accepted solution]

@ZoeM updated workflow attached

[ZoeM]

Your solution works. What about after the select tool? When the field is a double?

So then I need to change the field type first to get it to work.

We really cant do any data cleansing on a Double field type?

[binuacs]

@ZoeM Your "Value" field is a string type even after I applied the Regex formula the type did not change, only the formula removes the unwanted field but the fields type still string, and you should use the select tool to change it into double