Format columns

[JDong]

Hi Team,

I have the 2 columns in my data.

Price column is to be formatted with a thousand separator and on 2 decimal for all values. Example : 6.3211,00.

Average column is to be formatted as 2 decimals Ex : 0,06

The final field is needed as a string. 

Price	Average
63221	0
7805	0
38245	0
33562	0
30440	0
78052	1
24976	0
1561	0
12488	0
24196	0
7805	0
0	0
122541	1
56197	0
243521	2
485482	5
170152	1
9366	0
9366	0
0	0
27318	0
5463	0
0	0
0	0
266152	2
94441	0
117074	0
195128	2
61660	0
34342	0
51513	0
17951,96	0,23
13268,84	0,17
5463,64	0,07
21074,04	0,27
41367,56	0,53
28098,72	0,36
5463,64	0,07
11707,8	0,15
3902,6	0,05
35903,92	0,46
24976,64	0,32
37464,96	0,48
8360150

Please advise. Also to note the column already has different values but need to format and make them consistent. Thanks

[PhilipMannering]

Hey @JDong 

This seems like an overkill way of doing it, but I think it works,

[JDong]

Thanks @PhilipMannering 

The expanded decimals have actual values in the dataset. So it always should not round to ,00 but use the decimal points to arrive at relevant values

[AdamR_AYX]

I think this formula does what you want

ToString(ToNumber([_CurrentField_], 0, 1, ","), 2, ".", ",")

ToNumber parses the strings to numbers with a , for the decimal separator

Then ToString parses that number back to the format you want.

[JDong]

Hi @AdamR_AYX 

There is an error with the flow on the string conversion, could you please help !

Thanks

[AdamR_AYX]

Is this one any better?

What version of Alteryx are you on?

[JDong]

Hi  @AdamR_AYX 

Sorry still same and I use both 2019.1 and 2020.3

Thanks

[AdamR_AYX · Accepted solution]

OK sorry just checked and this was a new feature in 2020.4

https://help.alteryx.com/release-notes/designer/designer-20204-release-notes

I can do it in one formula in previous versions. It's just an ugly formula:

Replace(Replace(Replace(ToString(ToNumber(Replace([_CurrentField_], ",", ".")), 2, 1),
".", "¬"),
",", "."),
"¬", ",")

To walk through it (from the inside out)

We replace , with . and then convert to a number

Then convert the number back to a string with 2 decimal places and thousand separator. But , for the thousand separator and . for decimal separator.

Then replace the . with a ¬ (temporary placeholder for this character)

Replace the , with a .

Finally replace the ¬ with a ,

[JDong]

Hi @AdamR_AYX 

Thanks an excellent solution...one question..when we use a multi-field tool, is it possible to replace original field values instead of creating new columns ?

[AdamR_AYX]

Yes just uncheck this box