Formula Tool: Conditional formula

[jmorris24]

I don't understand why this isn't working. I literally used this same formula earlier in this flow. But everytime I type in ENDIF it makes the end go grey and throws out an error. Please help.

 

[jdunkerley79]

I've seen the formula tool do this before when the expression reaches a certain complexity.

I'd suggest simplifying the expression:

IF [Difference Start to Transaction] = '0'
THEN "A - Original Purchase"

ElseIf
[Difference Start to Transaction] IN ('1','2','3')
THEN "B - 1st Q"

ElseIf
[Difference Start to Transaction] IN ('4','5','6')
THEN "C - 2nd Q"

ElseIf
[Difference Start to Transaction] IN ('7','8','9')
THEN "D -3rd Q"

ElseIf
[Difference Start to Transaction] IN ('10','11','12')
THEN "E -4th Q"

Else
"None"

ENDIF

as that might help the UI.

If you can post the workflow with the issue happy to look

[jmorris24]

I tried that but I am getting the same error. I can't share the flow because it is a bunch of transaction data.  But here is a screen shot of it.

Any other ideas?

 

 

[jdunkerley79]

Odd - does the expression actually work even if the UI is failing to render?

Which version of Designer are you using?

[jmorris24]

No. It isn't working. 

Version Alteryx Designer 2020.3 x64

I am up against a deadline and this is brutal. I think I am going to manually create a lookup table in excel and then join back in. What can I do to get this fixed? It doesn't look like Alteryx allows me to submit a ticket for a bug.

[jdunkerley79]

Yes quickest work around is a lookup table in a Text Input

Then use a Find and Replace to append a column

Finally a formula tool to fill in the NULLs with None

Alternative formula which might work is a switch statement.

SWITCH([Difference Start to Transaction],'None',
 '0',"A - Original Purchase",
 '1', "B - 1st Q",
 '2', "B - 1st Q",
 '3', "B - 1st Q",
 '4', "C - 2nd Q",
 '5', "C - 2nd Q",
 '6', "C - 2nd Q",
 '7', "D -3rd Q",
 '8', "D -3rd Q",
 '9', "D -3rd Q",
'10', "E -4th Q",
'11', "E -4th Q",
'12', "E -4th Q"
)

[Maskell_Rascal · Accepted solution]

Hi @jmorris24 

Get rid of the quotes surrounding your numbers in either your original formula or the new one that @jdunkerley79  provided. The error is being caused by using a logic formula to match a number asked as a text. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[jmorris24]

That was it. Thanks!

So if you don't use any quotes for a number and you use " double quotes for text. Do you ever use ' single quotes in alteryx?

[Maskell_Rascal]

@jmorris24 Either single or double quotes are acceptable to use for text. Its really up to user preference there. If your numbers were being stored as a string, you would then want to use quotes in your formula like you initially had.  

[jmorris24]

that's helpful. thanks again!