How to keep a value till the first two non zero numbers after decimal
- 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
Hello friends,
I have a column with numbers. What I want is if the value is greater than or equal to 1 then it should be rounded by two decimal points. And if the value is less than 1 then it should be rounded till the first two non-zero digits. Here is the example of the data and how the output should be?
Strength | Output |
0.300 | 0.3 |
0.3300 | 0.3 |
0.0750000 | 0.075 |
0.075 | 0.075 |
0.001 | 0.001 |
0.0004 | 0.0004 |
0.00025 | 0.00025 |
0.000025000 | 0.00025 |
4.0023 | 4 |
23.2756 | 23.28 |
Can someone please help?
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Tougher than I expected!
Try this:
IF [Strength] >= 1 THEN Round([Strength],0.01)
ELSE '0.'+(PadLeft((Left(ToString(Round(ToNumber(PadRight(ToString(ToNumber(Replace(ToString([Strength]),'.',''))),3,'0')),10)),2)),(ToNumber(FindString(ToString([Strength]),Left((Left(ToString(Round(ToNumber(PadRight(ToString(ToNumber(Replace(ToString([Strength]),'.',''))),3,'0')),10)),2)),1)))),'0'))
ENDIF
The principal is:
- If >= 1 round to 2 decimal places
- If < 1 convert to a string, remove the '.' and convert back to a number (this gets rid of all the leading 0s and leaves everything from the first non-zero digit onwards) i.e. 0.0000000000789 -> 789
- Round to 2 decimal places - we now have the final two non-zero characters including any rounding i.e. 789 -> 79
- Calculate the number of leading zeros by finding the position of the first non-zero character in the string version of the original value (e.g. the 7 is character 13)
- Pad with the appropriate number of zeros and prepend with '0.' then convert back to a number e.g. '0.' + '000000000079' -> 0.000000000079
There's a lot of switching between numeric and string datatypes within the formula and you could probably clean this up a bit
Give it a go and let me know if it works. NB row 8 of your suggested output didn't seem consistent with what you were asking for, so I assumed that you'd want to reflect the correct number of leading zeros in that case.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Interesting challenge
Should the second line expected answer be 0.33?
Using a little maths, I think:
round([Strength],min(0.01,pow(10,Floor(LOG10([Strength])) - 1)))
will do what you want
For all cases a minimum of 2 dp will be used. For smaller cases it will shift downwards as needed.
Have attached as a sample
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Pramod91 — Try this solution: (btw, how come "0.3300" to "0.3"?)
IF ToNumber([Strength]) >=1 THEN
ToString(Round(ToNumber([Strength]), 0.01))
ELSEIF ToNumber([Strength]) <1 THEN
REGEX_Replace([Strength], "([0-9.])([0]+$)", "$1")
ELSE
[Strength]
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey, thanks for the reply. But when I run the workflow, I get an error "Error: Formula (3): Parse Error at char(40): Type mismatch. String provided where a number is required. (Expression #1)"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@vizAlter Thank you so much. Your formula gives me the exact output I wanted.
Can you please explain a bit about the formula so that I can modify and use it in future for different cases?
Especially the the second part of the formula after Regex_
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Got to say you can do a lot of magic using Regex.
There is so much to explore
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Pramod91 — Sure, PFB:
REGEX_Replace([Strength], "([0-9.])([0]+$)", "$1")
Here for the "pattern" logic:
The 1st Group ( ) reads all the digits from 0 to 9 and "." char as well. Since, we don't know the position of the decimal hence I have mentioned in the [ ]
[abc] | Matches any character (same as (a|b|c)) |
And the last Group ( ) is also based on the above logic if "0" is available or not, but tracks from the end point.
For "replace" position:
"$1" is taken as the result which we need
(since we have two groups so try to put "$2", and check how the RegEx behaves; just to understand more if you want)
Btw, here is a good guide by Alteryx:
https://help.alteryx.com/2020.2/Reference/Functions.htm
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@atcodedog05 — True 🙂
