cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Formula help

Meteor

Can you please help me with a formula to get the result on the right?  I tried an IF CONTAIN formula, but it's not working.

 110001 110000
Meteor

I have a lot of values containing .6 and want to change them to .3

Alteryx Certified Partner

There's a little converting that you'll need to do for those string functions to work on that numeric value. Try this:

tonumber(replace(tostring([Field1]),".6",".3"))

Formula Breakdown:

1) You're going to replacing the string ".6" with ".3", but this has to be performed on a string value. Convert the value ([Field1]) using "tostring([Field1])".

2) Then perform the replacement using "replace(tostring([Field1]),".6",".3")"

3) Once you have that replacement done, convert the final result back into a numeric value using "tonumber("

Meteor

Can you provide a workflow?

Meteor

I got it to work, but I have a new problem.  How do I add two or more conditions so that any value containing .6 is updated to .3, .61 is updated to .31, and any value with no decimal is updated to .2?

Alteryx Partner

I wouldn't convert to string, I'd just do this:

Code is:

``````if
round([Field1] -  floor([Field1]),.1) = 0.6
then [Field1] - 0.3
else [Field1]
endif``````
Highlighted
Alteryx Certified Partner

I've attached a workflow that shows off a a few tools that can be used to achieve this. Let me know if you have any questions.

Alteryx Partner

Can you test this please ?

``````if
round([Field1] -  floor([Field1]),.2) = 0.61 or round([Field1] -  floor([Field1]),.1) = 0.6
then [Field1] - 0.3
elseif floor([Field1]) = [Field1] then [Field1] + 0.2
else [Field1]
endif``````
Meteor

I got a red error "Type mismatch in operator +".  Do you know how to fix?

Alteryx Partner

It's working for me ....

Labels