We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

## Formula Error Between IF statements.

8 - Asteroid

Hi, if you have a moment, can you look at my formula? I know I'm making a silly mistake but I can't seem to figure it out at the moment. All of the results are showing up as error:

If Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "CAN British Columbia- Purchases- GST")
&& Contains ([Input Tax], "5%")
THEN
[Input Rate] = "GST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "5%")
THEN
[Input Rate] = "GST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "CAN Ontario HST (13%) (13%)")
&& Contains ([Input Tax], "13%")
THEN
[Input Rate] = "HST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "0%")
THEN
[Input Rate] = "Exclude"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "7%")
THEN [Input Rate] = "Exclude"
ELSE 'ERROR'
ENDIF

7 REPLIES 7
12 - Quasar

The issue is in your 'THEN' clauses. You want to set the column name to Input Type, and then remove anything that says

``[Input Type] = ``

in your formula. Your formula should look like this:

``````If Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "CAN British Columbia- Purchases- GST")
&& Contains ([Input Tax], "5%")
THEN
"GST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "5%")
THEN
"GST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "CAN Ontario HST (13%) (13%)")
&& Contains ([Input Tax], "13%")
THEN
"HST"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "0%")
THEN
"Exclude"
ELSEIF

Contains([Tax Type], "Tax Paid")
&& Contains([Tax Applicability], "zzz_DNU CAN British Columbia PST (7%) / GST (5%) (12%)")
&& Contains ([Input Tax], "7%")
THEN "Exclude"
ELSE 'ERROR'
ENDIF
``````

Let me know if this doesn't work or if you have any questions!

20 - Arcturus

I feel it would better to have a master index table for this case.

The long if else statements are very difficult to maintain.

8 - Asteroid

@Qiu  I'm  not really sure what you mean. How can I tell my workflow to base a decision on a master index?

8 - Asteroid

Hi @kelsey_kincaid I hope all is well.

Alteryx accepts your formula but now I need to round the percentages before using the formula. I found the a few formulas in the community discussions but these are also not being accepted. I tried the two that I thought would meet the requirement.

20 - Arcturus

Sorry and now I am confused.

The one I proposed is only to convert your long if else statement to a master index.

19 - Altair

The issue that you're seeing here is that the [Input Tax] is a string field and you're trying to multiply it by 100.

Use ToNumber([Input Tax]) in your formula.   Since you're also looking for integer percentages, i.e. "7%" in the if statement from your initial post, use a formula like this

``ToString(Round(ToNumber([Input Tax])*100,1))+"%"``

Working from the innermost brackets out, this formula

1. converts [Input Tax] from a string to a number and then multiplies by 100

2. rounds the results to an integer value

3. converts it back to a string and appends "%"

Dan

8 - Asteroid

Hi @Qiu , I apologize. I was saying that I did not understand your proposed solution. I'm not entirely sure how a master index solves the original question. I understand that a master index would require less maintenance, but i don't understand how I would get the same result. Maybe I just have to look up how to incorporate a master index. This is entirely new to me. Again, I apologize.

Labels