How do I keep a column as string however change decimals to 3?

Question

I have a dataframe as from csv:

AddressCustomer NumberName1 /209 CORMACK ROAD1011.129Res Wingfield  - Sa/Nt1 /209 CORMACK ROAD1011.129Rexel Port Wingfield1 /209 CORMACK ROAD7117484Rexel Port WingfieldUNIT 1/209 CORMACK RDSUEZWINGRexel Port Wingfield1 STURT HIGHWAY1011.034Res Berri  - Sa/Nt1 STURT HIGHWAY1011.034Rexel Berri1 STURT HIGHWAY50056061Rexel Berri1/120 BATT STREET1011.017John R Turk Penrith1/120 BATT STREET1011.017Jrt Penrith  - N1

Alteryx is reading the same as:

Address Customer Number Name
1 /209 CORMACK ROAD 1011.129 Res Wingfield - Sa/Nt
1 /209 CORMACK ROAD 1011.129 Rexel Port Wingfield
1 /209 CORMACK ROAD 7117484 Rexel Port Wingfield
UNIT 1/209 CORMACK RD SUEZWING Rexel Port Wingfield
1 STURT HIGHWAY 1011.034 Res Berri - Sa/Nt
1 STURT HIGHWAY 1011.034 Rexel Berri
1 STURT HIGHWAY 50056061 Rexel Berri
1/120 BATT STREET 1011.0170000000001 John R Turk Penrith
1/120 BATT STREET 1011.0170000000001 Jrt Penrith - N1

(pardon the formatting as I have just copy pasted from the browse tool)

Please note the customer number changed from 1011.017 to 1011.0170000000001 which I dont want!

Also, I just cannot change it in the select tool to be decimals as there are text fields as well e.g. SUEZWING and they get deleted when I change it in the select tool.

What is the easiest way to maintain the sanity of the dataframe while porting it over in Alteryx?

danilang · Answer

Hi @HW1

As you've seen, if you change the column type to a numeric one, all the string values will be replaced with null.  What you need to do is apply string manipulations to the existing data without changing the field type

The attached workflow uses this formula to truncate the product IDs 3 characters after the decimal point, but only if there is a decimal.  If not it just uses the existing product ID

if Contains([Customer Number], ".") then
	substring([Customer Number],0,findstring([Customer Number],".")+4)
else
	[Customer Number]
endif

Dan

3DecimalsInStringColumn.yxmd

Kurohits · Answer

What I meant by Spills to check data shifts or row shift. Sometimes sanity of the data breaks when dragging to alteryx.

HW1 · Answer

Unfortunately, this is not just one value as text. there are many hence the workflow to address this issue.

Also, 
Can you please explain what do you mean by "Spills in the data"?