We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Formula Error Between IF statements.

jalizmaldonado
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
kelsey_kincaid
11 - Bolide

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
19 - Altair
19 - Altair

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

jalizmaldonado
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? 

Formula rounding issue.JPG

rounding requirement.JPG

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

rounding requirement.JPG

Formula rounding issue.JPG

Qiu
19 - Altair
19 - Altair

@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
18 - Pollux
18 - Pollux

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.  

danilang_0-1619353827802.jpeg

 

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