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

Input Tool adding additional decimals to .xlsx file

Matt_Kerr
6 - Meteoroid

When I import the attached .xlsx file I get two different values if the column starts with a string or number. I need for the input to show below. Is there a way to get Alteryx to bring in it all as string and not get all of the additional decimal places? 2019-12-08_16-07-12.jpg

 

 

 

String and Double
String
10200.2
9 REPLIES 9
DiganP
Alteryx Alumni (Retired)

@Matt_Kerr You want to use this function in the formula tool. Make sure the data type is double.

 

ToNumber([String and Double]).

 

This will give you the numbers as a double while turning text into 0. Is this what you were looking for?

 

Capture.PNG

 

 

Digan
Alteryx
Matt_Kerr
6 - Meteoroid

@DiganP  Unfortunately not. I need it to show the string for the first row, then 10200.2 for the 2nd row.

BrandonB
Alteryx
Alteryx

Will there ever be numbers in the string value that you want to return? If not, you could use REGEX_Match() to check for a digit and a conditional formula that converts the value to a number if so or doesn’t if not.

DiganP
Alteryx Alumni (Retired)

@Matt_Kerr Attached is the workflow with the logic you might use. You can use the tonumber function first then the conditional statement. 

 

if [Test]=0 then [String and Double] else [Test] endif

Digan
Alteryx
Matt_Kerr
6 - Meteoroid

I actually have some other data within that column that I need to stay as is but unfortunately those formulas change the data

 

I have attached the Excel file. I need need the first column to come in as a string in alteryx (looking like it does in excel).

Matt_Kerr
6 - Meteoroid

I was able to resolve it with the following formula:

 

if REGEX_Match([String and Double],"\d+\.\d+")
then tonumber([String and Double])
else [String and Double]
endif

 

Thanks,

aeking
5 - Atom

I had a similar issue with Alteryx adding extra decimal spaces, but after applying this REGEX formula I noticed a new issue. Some items in my list have leading or ending zeros and a decimal place. So '0123.678' is becoming '123.678' and '385.3900' is becoming '385.39'. 

Is there a way to correct Alteryx creating the extra decimal spaces while also keeping leading and ending zeros where they exist?

BrandonB
Alteryx
Alteryx

@aeking numbers by definition cannot have leading zeroes and trailing zeroes are additional precision placeholders. If you need these leading and trailing zeroes you should likely keep your values as strings rather than converting to numbers. 

vaughangary
8 - Asteroid

What about 03 as text into Excel?  If you type it directly into Excel, it is '03 (showing as 03).  From alteryx, i only get 03 from 03 or '03 from '03.  Neither replicate the Excel scenario where '03 shows as 03.

Labels