I have a column with string and numerical values which includes decimals too.
I want to maintain the integrity of these columns while converting the recurring decimal numbers to proper numbers like:
1068.06999999999
999.000999999999
999.001999999999
999.005999999999
Should be
1068.07
999.001
999.002
999.006
I am trying regex identify and convert however I am not getting the right output
I dont know how to correctly do the same
Please find example workflow attached.
Hi @HW1, the formula you wrote in the workflow is correct you just need to use round function in order to achieve the desired result.
Here is the formula:
IF REGEX_CountMatches([Customer Number], '^(?!\d+(?:\.\d+)?$).*$') = 0 then Round(ToNumber([Customer Number]),0.01) else [Customer Number] endif
I hope this helps!
Thanks!
@HW1 Another way of of doing this with the below formula
IIF(Contains([Customer Number], 'E'),[Customer Number],toString(toNumber([Customer Number]),2))
Hi, @HW1
maybe you need the dynamic solution.
Input | Output | |
1009.00699999999 | 1009.007 | |
1068.02199999999 | 1068.022 | |
1068.02399999999 | 1068.024 | |
1068.04099999999 | 1068.041 | |
1068.06999999999 | 1068.07 | |
999.000999999999 | 999.001 | |
999.001999999999 | 999.002 | |
999.005999999999 | 999.006 | |
0 | 0 | |
0E003 | 0E003 | |
0E004 | 0E004 | |
0E006 | 0E006 | |
0E007 | 0E007 | |
0E009 | 0E009 | |
0E00B | 0E00B | |
0E00D | 0E00D | |
0E00E | 0E00E | |
0E00F | 0E00F | |
0E00H | 0E00H | |
1455.02 | 1455.02 | |
1455.021 | 1455.021 | |
1455.022 | 1455.022 | |
1455.023 | 1455.023 | |
1455.025 | 1455.025 | |
1455.026 | 1455.026 | |
1455.027 | 1455.027 | |
1455.028 | 1455.028 | |
1455.029 | 1455.029 |
Hi @flying008 Can you please help me with an example workflow of the solution? I think this works best as I have provided a simple sample of more varying examples. This can be most helpful.
Thanks
This solution is forcing the decimal values to whatever the user defines. While I am looking for a solution that works when there are recurring decimals and that needs to be restricted.
Thanks