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:
Solved! Go to 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!
@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.
@Qiu I'm not really sure what you mean. How can I tell my workflow to base a decision on a master index?
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.
@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?
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
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.