How do I correctly convert recurring decimals properly?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Help
- Parse
- Regex
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@HW1 Another way of of doing this with the below formula
IIF(Contains([Customer Number], 'E'),[Customer Number],toString(toNumber([Customer Number]),2))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
