Formula Error Between IF statements.

[jalizmaldonado]

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

[kelsey_kincaid · Accepted solution]

Hi @jalizmaldonado ,

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!

[Qiu · Accepted solution]

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

The long if else statements are very difficult to maintain.

[jalizmaldonado]

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

 

[jalizmaldonado]

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. 

[Qiu]

@jalizmaldonado 
Sorry and now I am confused.

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

Then your reply raised another question?

[danilang · Accepted solution]

Hi @jalizmaldonado 

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

[jalizmaldonado]

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.