Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Trim a number to just the decimals

Highlighted
Alteryx Certified Partner

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?

 

Alteryx Certified Partner
Alteryx Certified Partner

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. 

 

SubString(ToString([YoS]),FindString(ToString([YoS]),"."))

 

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

Highlighted
14 - Magnetar
14 - Magnetar

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!

Highlighted
Alteryx Certified Partner

Your assumptions were right!  Thank you for your help.

 

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

Highlighted
Alteryx Certified Partner

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

Labels