convert v_string to something i can add

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

Input

Accepted answers

MarqueeCrew

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

Or

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

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

Cheers,

Mark

vkarthik21

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], ",", ""))

All comments

MarqueeCrew

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

Or

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

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

Cheers,

Mark

vkarthik21

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335