Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

error converting txt document string fields to numbers

dsouthh
7 - Meteor

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? 

6 REPLIES 6
JordanB
Alteryx
Alteryx

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
7 - Meteor

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.

KenMoorhead
7 - Meteor

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

dsouthh
7 - Meteor

Thanks for explaining why I'm getting -1. 

 

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

dsouthh
7 - Meteor

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

dsouthh
7 - Meteor

For those who also run into my original issue, I highly suggest the auto-field as Jordan mentioned. I didn't know it existed and is a much better way of converting than the multi-feild tool

Labels