How to keep a value till the first two non zero numbers after decimal

[Pramod91]

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?

[jamielaird]

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:

1. If >= 1 round to 2 decimal places
2. 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
3. Round to 2 decimal places - we now have the final two non-zero characters including any rounding i.e. 789 -> 79
4. 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)
5. 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.

[jdunkerley79]

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

[vizAlter · Accepted solution]

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

[Pramod91]

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)"

[Pramod91]

@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_

[jdunkerley79]

The error is due to Strength being a string. An adjustment to:

round(ToNumber([Strength]),min(0.01,pow(10,Floor(LOG10(ToNumber([Strength]))) - 1)))

will fix this. Converting the value of [Strength] to a number before using it.

Have updated the sample 

[atcodedog05]

Got to say you can do a lot of magic using Regex.

There is so much to explore

[vizAlter]

@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

[vizAlter]

@atcodedog05 — True  🙂