error converting txt document string fields to numbers

Hey guys,

Even though the .txt file type is not supported, I'm able to import my .txt file successfully since it is tab delimited.

I've verified that my .txt file is successfully imported by looking at the output of my input data tool. However, all of the fields are read-in as strings. So, instead of using the select tool and converting each of my fields to doubles (since I have hundreds of fields), I'm trying to use the multi-field tool.

In the multi-field tool, I don't select the "Copy Output Fields Add" option but I do choose the "Change Output Type to Double" option in the configuration window.

For my expression I use: "[_CurrentField_] = [_CurrentField_]".

When I do this, all the fields get set to -1.

Does anyone know why this is happening?

Input

Tips and Tricks

Preparation

Accepted answers

JordanB

Hi @dsouthh

I would strongly suggest using the auto field tool in Alteryx to evaluate your data within the fields to assign the best possible data type and size for processing.

Alternatively you can use the Change field type of highlighted fields within the select tool to change the data type at one time. Find this choice within 'Options' in the configuration window once you highlight all the fields.

For the Multi-field you would have to use this expression: tonumber([_CurrentField_])

Best,

Jordan Barker

Solutions Cosnultant

KenMoorhead

Using [_CurrentField_] = [_CurrentField_] in the expression will always return a value of -1. That expression is a boolean (True/False) comparison, in this case it always evaluates to True.

For example, if you have a field with the value 3... the formula tool will evaluate the expression 3 = 3 and return True (-1).

I would suggest using ToNumber([_CurrentField_]) to best handle the conversion you're looking for. You could set the expression to just [_CurrentField_], but it will throw an error if the field you're changing contains a mix of numeric and string values. Using the ToNumber() function inside the expression will handle strings by setting the value to 0.

Attached is a sample workflow showing your current multi-field formula as best I understand it, and demonstrating the use of just [_CurrentField_] and ToNumber([_CurrentField_]) as expressions in the multi-field formula tool.

String to Double Conversion Sample.yxmd

All comments

JordanB

Hi @dsouthh

I would strongly suggest using the auto field tool in Alteryx to evaluate your data within the fields to assign the best possible data type and size for processing.

For the Multi-field you would have to use this expression: tonumber([_CurrentField_])

Best,

Jordan Barker

Solutions Cosnultant

KenMoorhead

Using [_CurrentField_] = [_CurrentField_] in the expression will always return a value of -1. That expression is a boolean (True/False) comparison, in this case it always evaluates to True.

For example, if you have a field with the value 3... the formula tool will evaluate the expression 3 = 3 and return True (-1).

String to Double Conversion Sample.yxmd

KenMoorhead

@jordanb I agree, I would look to use the AutoField tool or Select tool ahead of the Multi-Field formula for this case.

dsouthh

Thanks for explaining why I'm getting -1.

The rest of your suggestion makes complete sense! My workflow is perfect now. Thank you.

dsouthh

Thank you Jordan! I'll check out the auto field tool now.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703