I have a dataframe as from csv:
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.017 | John R Turk Penrith |
1/120 BATT STREET | 1011.017 | Jrt 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?
Hi @HW1
Can you please share the sample data in excel and also screen shot of the tool configuration ?
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.
And when I change the decimal to fixed decimal, I am getting it null as expected but I want the text field
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.
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"?
What I meant by Spills to check data shifts or row shift. Sometimes sanity of the data breaks when dragging to alteryx.
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