Hello,
I currently have three columns, from which I want to put a Conditional Formula against.
The problem is this particular conditional formula is quite exhaustive, in terms of IF XXX then "YYY". I also want to future-proof without having to make changes to the workflow, and a user will only need to change an input file (xlsx instead).
Code | Name | Company | Company Code |
1234 | Bob | Tesla | TES |
2345 | Ted | Honda | HON |
3456 | Allan | Harley Davidson | HDM |
4567 | Stu | Ducati | DUC |
Aoi | Norton | NOR | |
6789 | Louis | Renault | REN |
Now, this Company list lets say is exhaustive.
I have a separate mapping table which essentially goes like this:
Company | Auto Type |
Tesla | Cars |
Honda | Cars |
Harley Davidson | Motorcycles |
Ducati | Motorcycles |
Norton | Motorcycles |
Renault | Cars |
Hyundai | Cars |
And another like this:
Company Code | Auto Type |
TES | Cars |
HON | Cars |
HDM | Motorcycles |
DUC | Motorcycles |
NOR | Motorcycles |
REN | Cars |
HYU | Cars |
My condition formula is built like this:
[Test Column]
IF [Code] in ("1234","2345","9999","2357") then "Cars"
ELSEIF isnull([Code]) and Left([Name], 1) = "B" then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and contains([Company], "Automotive") then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and [Company] in ("Honda", "Hyundai", "Renault", "Tesla") then "Cars"
ELSEIFisnull([Code)] and [Name] = "Ted" and [Company] in ("Harley Davidson", "Ducati", "Norton") then "Motorcycle"
ELSEIF isnull([Code)] and [Name] = "Ted" and [CompanyCode] in ("HON", "HYU", "REN", "TES") then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and [CompanyCode] in ("HDM", "DUC", "NOR") then "Motorcycle"
Else "Review"
Endif
I ideally, want something like this though (see below), because both my "[Company] in (" XXX ") etc is actually alot longer, and like I said above, I want a user to not have to make changes in the Alteryx Workflow.
[Test Column]
IF [Code] in ("1234","2345","9999","2357") then "Cars"
ELSEIF isnull([Code]) and Left([Name], 1) = "B" then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and contains([Company], "Automotive") then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and [Company] in {Company mapped to Cars} then "Cars"
ELSEIF isnull([Code)] and [Name] = "Ted" and [Company] in {Company mapped to Motorcycle} then "Motorcycle"
ELSEIF isnull([Code)] and [Name] = "Ted" and [Company] in {CompanyCode mapped to Cars} then "Cars"
ELSEif isnull([Code)] and [Name] = "Ted" and [Company] in {CompanyCode mapped to Motorcycle} then "Motorcycle"
Else "Review"
Endif
Note, that I can still make changes to the mapping table if needed to accommodate anything to make it easier (just no the original data extract).
Anyone know how to make sense of the above into an Alteryx Workflow?
Solved! Go to Solution.
I want to add, that I can use a Dynamic Formula tool to add the Condition Formulas and this would allow a user ability to update the workflow without actually touching it.
However, would prefer to keep this as a last resort, as I do not want external user to add the IF statements etc.
Hi @R_L,
I'd just use the Find Replace - Tool here and do not use some sort of a formula. A workflow would look like this:
I added a formula in case one matching table applies. The result looks like this:
I'll attach the workflow for you. Let me know what you think!
Best
Alex
Thanks @grossal. Didn't use your exact workflow, but it did give me inspirations!
Took your approach and included the Find/Replace and filter out for Nulls, but only after I've applied some necessary conditional formulas.