Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Trim a number to just the decimals

9 - Comet

I suspect this can be done with Regex.  I need to get just the decimals.


I'm doing HR Analytics, and looking at employees Years of Service.  We measure Years of Service as 4.92810010910034.  I'm trying to use this field to gauge how close an employee is to their anniversary.  Obviously this person is very close to their anniversary.  So, all I need is the section after the decimal.  


So, how do I trim this number?


17 - Castor
17 - Castor

Hi @Charity_K_Wilson !


A non-RegEx method would be to use the SubString( function to find the ".". Here's an example, where [YoS] is the "Years of Service" Field. 




I've attached an example of this in action too. 

15 - Aurora
15 - Aurora

Hi @Charity_K_Wilson 


There are a couple ways to accomplish this depending on how many of those decimal places you want to keep after the decimal point. 


One way to do this would be to use a Formula tool with this expression:

Substring(tostring([Field1]),FindString(tostring([Field1]), ".")+1,4)

This will keep the first four digits after the decimal point.



If you wanted to try your hand at RegEx, you could also use a Formula tool with this expression:

REGEX_Replace(tostring([Field1]), ".*\.(\d+)", "$1")

This will keep all digits after the decimal point.



Note - in both of these cases, I am assuming your original field is a numeric type so I wrap that field name in tostring() so that I can use it in these expressions. 


Hope this helps!

9 - Comet

Your assumptions were right!  Thank you for your help.


And so neat to see the regex way of doing it.  

9 - Comet

Thanks Charlie.  That did exactly what I needed it to do.  I knew there had to be a quick formula way to do it.