Alteryx designer Discussions

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

Using a IF Condition Formula and a Find and Replace

Highlighted
7 - Meteor

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

 

CodeNameCompanyCompany Code
1234BobTeslaTES
2345TedHondaHON
3456AllanHarley DavidsonHDM
4567StuDucatiDUC
 AoiNortonNOR
6789LouisRenaultREN

 

Now, this Company list lets say is exhaustive.

 

I have a separate mapping table which essentially goes like this:

 

CompanyAuto Type
TeslaCars
HondaCars
Harley DavidsonMotorcycles
DucatiMotorcycles
NortonMotorcycles
RenaultCars
HyundaiCars

 

And another like this:

 

Company CodeAuto Type
TESCars
HONCars
HDMMotorcycles
DUCMotorcycles
NORMotorcycles
RENCars
HYUCars

 

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?

Highlighted
7 - Meteor

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.

Highlighted
13 - Pulsar

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: 

 

grossal_0-1590048513242.png

 

I added a formula in case one matching table applies. The result looks like this:

 

grossal_1-1590048560240.png

 

I'll attach the workflow for you. Let me know what you think!

 

Best

Alex

Highlighted
7 - Meteor

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.

Labels