convert v_string to something i can add

[becki]

not certain what i'm doing wrong but i'm reading in a .csv file and all fields are size 254, type v_string that looks like :

2,000.00

i've tried converting this to fixed decimal, double, int and float and it seems that all of them fail because when I add 3 of these fields together, anything that had a comma in the number only has the first number, losing everything else.  What do I do to covert this or do i need to remove the comma before anything?

thanks

becki kain

[MarqueeCrew · Accepted solution]

Replace([datafield],",",'')

Or

Replacechar([datafield],",",'')

That being done, you can use a select to change the type to a number.

Cheers,

Mark

[vkarthik21 · Accepted solution]

You would need to first replace the comma (,) with blank and then use ToNumber function as seen below. [Field1] needs to be replaced with your field

TONUMBER(Replace([Field1], ",", ""))

[Treyson]

Just to clarify your problem. What you have is a string value within your CSV. The actual data in the cell has both the comma and the period. This isn't something you see through excel formatting in a pretty way.

When you have your data in this manner, Alteryx tries to convert those characters into a number. This is failing through a simple cast or changing the data types because it sees those characters are handled in the same way that it would see any letters or other special characters. The provided examples will help you get around those problems.

[becki]

thanks, that was it!  now, if a number is like 2,000, what should I set the type to:  int16, int32, float, etc..?

[becki]

is that more efficient to do that than a formula and then a select?  I don't know enough about alteryx to know about speed.  thanks

[vkarthik21]

It's not the question of the decimals. If your values are of the form 2,000.25 or 2,000 you would still need to perform the string manipulation and casting. If you are having multiple columns within your source system with such values I would suggest to use 'Multi-Field Formula' transform. Select all the fields of your input to this transform and set the expression as - 

TONUMBER(REPLACE([_CurrentField_], ",", ""))

If you need to make the process more efficient, you would first need to fix your source data. The system or process responsible for getting source data should be modified so that the amount values are obtained as Double instead of String. Once the data in CSV, there is no other efficient way of handling it other than treating it as string.