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.

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

HW1
9 - Comet

I have a dataframe as from csv:

 

AddressCustomer NumberName
1 /209 CORMACK ROAD1011.129Res Wingfield  - Sa/Nt
1 /209 CORMACK ROAD1011.129Rexel Port Wingfield
1 /209 CORMACK ROAD7117484Rexel Port Wingfield
UNIT 1/209 CORMACK RDSUEZWINGRexel Port Wingfield
1 STURT HIGHWAY1011.034Res Berri  - Sa/Nt
1 STURT HIGHWAY1011.034Rexel Berri
1 STURT HIGHWAY50056061Rexel Berri
1/120 BATT STREET1011.017John R Turk Penrith
1/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?

 

6 REPLIES 6
Kurohits
10 - Fireball

Hi @HW1 

 

Can you please share the sample data in excel and also screen shot of the tool configuration ?

HW1
9 - Comet

Hi @Kurohits  I am unable to duplicate this issue now as when I saved the data  in another csv as an example the problem is not there.

However, I am working on the issue, please find the screenshot of what I am trying to do.

 

HW1_0-1623304210455.png

 

And when I change the decimal to fixed decimal, I am getting it null as expected but I want the text field

 

HW1_1-1623304329628.png

 

 

Kurohits
10 - Fireball

Hi @HW1 

 

Thanks for sharing date preview. 

 

However the solution that you are looking for isn't directly possible. What you can do, after converting "Customer number" to fixed decimal, try to replace null values with "SUEZWING". I don't know if data contains only single text value then this will perfectly work otherwise if data has multiple text values then we need to use another method. Also i would suggest to check any splits and spills in the data. 

 

Let me know if you are unable to do. 

HW1
9 - Comet

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"?

Kurohits
10 - Fireball

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

danilang
19 - Altair
19 - Altair

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

Labels