topic Re: Formula help in Alteryx Designer Desktop Discussions

Formula help

htalteryx — Mon, 13 May 2019 20:52:24 GMT

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

110000.3

Re: Formula help

htalteryx — Mon, 13 May 2019 20:54:45 GMT

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

Re: Formula help

CharlieS — Mon, 13 May 2019 21:17:26 GMT

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

Re: Formula help

htalteryx — Mon, 13 May 2019 21:21:11 GMT

Can you provide a workflow?

Re: Formula help

htalteryx — Mon, 13 May 2019 21:38:23 GMT

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?

Re: Formula help

cmcclellan — Mon, 13 May 2019 21:44:22 GMT

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

Re: Formula help

CharlieS — Mon, 13 May 2019 21:45:47 GMT

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.

Re: Formula help

cmcclellan — Mon, 13 May 2019 21:48:43 GMT

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

Re: Formula help

htalteryx — Mon, 13 May 2019 21:59:36 GMT

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

Re: Formula help

cmcclellan — Mon, 13 May 2019 22:04:10 GMT

It's working for me ....

Re: Formula help

htalteryx — Mon, 13 May 2019 22:10:37 GMT

I got an error. How do I update the data type in the formula tool? It's grayed out for me.

Re: Formula help

cmcclellan — Mon, 13 May 2019 22:16:30 GMT

If you are changing the formula on an existing field you can't change the datatype in the Formula tool (use a Select tool before to achieve this). If you create a new field in the Formula tool then you can set the datatype.

Re: Formula help

htalteryx — Mon, 13 May 2019 22:27:47 GMT

Can you please look at my workflow I provided above and let me know how I can fix? Thank you so much for your help and continued patience!

Re: Formula help

cmcclellan — Mon, 13 May 2019 22:31:28 GMT

Don't create Field2 in the Text Input, create it in the Formula tool :)

see attached.

Re: Formula help

cmcclellan — Tue, 14 May 2019 21:20:24 GMT

Can you mark answer/s as correct if it provided the solution for you please ?

Re: Formula help

htalteryx — Tue, 14 May 2019 22:12:49 GMT

Can I email you if I have further questions? Or should I reply to the thread?

Re: Formula help

cmcclellan — Tue, 14 May 2019 21:31:55 GMT

Always better to post in the forums :) If you still have a problem with the formula then post here, if it's a new issue then start a new thread.

I just realised you are in Irvine as well, just down the road from Alteryx HQ :)