Data transformation- Specific cell

[ramsb]

Hi,

I am currently working with a data file that includes a column defined as a string type. However, some cells within this column contain numeric values that I would like to use for calculation purposes. Is it possible in Alteryx to convert specific cells—within a string-type column—to a numeric data type such as double?

I have attempted several formula-based approaches, but unfortunately, they did not yield the desired outcome to convert these cells to store double (number) values. Any guidance or suggestions on how to achieve this would be greatly appreciated

[jrlindem]

Double can't be used if there is text in the field.  If you need to do calculations where the field contains numbers, then consider splitting the column into two using formulas to differentiate.  One for the text values and one for the numbers.  Then once you've completed the calc's recombine into a V_String or V_Wstring so that it can house both/all types.

Edit:  As others have pointed out you can force the field to act as numbers using ToNumber() and then assign the field as Double, but simply designating the field that way ahead of time will result in Null values.  Watch out for the ToNumber() convversion creating zero's.  That may or may not be useful when doing calculations.

-Jay

[Pilsner]

Hello @ramsb,

In Alteryx, data types are assigned on a column basis, meaning it is not possible (as far as I'm aware) to store specific cells within the same column, as different data types. It is, however, still possible to implement numeric operations successfully, using a string column. 

In the formula tool, the ToNumber function can be used to convert numbers stored as a string to a number, on a cell-by-cell basis. If you take a string column and apply the ToNumber() function to it, one of two things will happen.

1) If it was in fact a number, labelled as a string, it will temporarily convert it to a numeric data type for processing within the formula tool. 
2) If it were a string value, it would return as empty.

Using this information, you can apply numeric operations to string columns. In the blow example, I wanted to multiply the string column by the Numeric column. 

As you can see, this successfully worked for all of the "numeric cells" within the "String" column.

I've attached my workflow below incase you wanted to take a look. 

Please let me know if this helps. 

Regards - Pilsner

[cjaneczko]

You can use tonumber([String Field]) to convert all of the ones with numbers in the rows to number and do whatever math you need to. Any of the ones without numbers wont be impacted.