Formula Error Between IF statements.

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

Workflow

Accepted answers

kelsey_kincaid

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

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

0423-jalizmaldonado.yxmd

danilang

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

All comments

kelsey_kincaid

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

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

0423-jalizmaldonado.yxmd

jalizmaldonado

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

Formula rounding issue.JPG

rounding requirement.JPG

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.

rounding requirement.JPG

Formula rounding issue.JPG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703