Using a IF Condition Formula and a Find and Replace

[R_L]

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?

[R_L]

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.

[grossal · Accepted solution]

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

[R_L]

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.