community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
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.

110000.6110000.3
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:

 

2019-05-14 07_36_52-Alteryx Designer x64 - New Workflow7_.png

 

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 .... 

 

2019-05-14 08_03_01-Alteryx Designer x64 - New Workflow7_.png

Labels