Double Data Conversion Error

[grsomer]

Hi! 

I am trying to convert some data that is being read in as String to Double. The data is numeric, but contains commas, decimal places and negative signs, and I believe this is why it is being read in as string. When I change the data to double in the Select tool, the data read is cut off after the first decimal place. Is there a way to change my data to double while still keeping the decimals, commas, etc.? I have tried the Data Cleanse tool, but this takes out the decimals and negative signs, obviously making the numbers wrong. I need the data to be read as numeric in the output, so that I can preform further formulas in Excel. 

Thanks!

[Thableaus]

Hi @grsomer 

Could you please show us a sample of your data and how it looks like?

Cheers,

[grsomer]

Hi @Thableaus 

This is a small sample of my data, unfortunately I can't share too much due to confidentiality. Hope this helps. 

[danilang · Accepted solution]

Hi  @grsomer 

Within Alteryx, there's no way for you to keep the commas with a double field type.  You can use a formula like this, output to a new double field, to remove just the commas

ToNumber(replace([stringfield],",",""))

This should return your numbers with the decimal and negative signs intact.

You also mentioned that Data Cleanse removed the negative and decimals, but sample you provided included those.  Can you share a small sample of the data in the source, before it comes into Alteryx?

Dan

[Thableaus]

@grsomer 

Try to use the Fixed Decimal type instead.

You can read more about it here: https://help.alteryx.com/2018.2/Reference/DataFieldType.htm

This way you probably don't lose precision. Just be careful handling them. A good idea would be setting a fixed length to define your precision. 

Cheers,