Alteryx Cleaning Data

Hi Guys,

I'm looking for a dynamic way to search a column of supplier names and clean it. So below it would just be amazon. Every month my data updates with additional suppliers like the below.

I dont want to use a find and replace as every month I will have new iterations of the same supplier name in my database.

Is there a way of searching a sring and if contains "Amazon" then Supplier Name = "Amazon"

Supplier Name

Amazon 1

Amazon 2

Amazon 3

Amazone January

Amazone February ..

Thanks

Custom Tools

Accepted answers

Emil_Kos

Hi @Tommy_Alterax,

Hope you are well.

This is the formula that you need:

IIF(Contains([Name], 'Amazon'), 'Amazon', [Name])

I have prepared a workflow for you.

Please mark this post as a solution if this is helpful.

Good Luck!

Replace.yxmd

All comments

Emil_Kos

Hi @Tommy_Alterax,

Hope you are well.

This is the formula that you need:

IIF(Contains([Name], 'Amazon'), 'Amazon', [Name])

I have prepared a workflow for you.

Please mark this post as a solution if this is helpful.

Good Luck!

Replace.yxmd

marcusblackhill

Hey @Tommy_Alterax !

Well, so you want to do a filter in your data to have only the record with the supplier name you want, right?

One thing you can do is to filter and with the operands "contains". Select one value just to have one already set, but you can automate that filter with text box tool of the interface group tools and link that with filter using a action tool, that way you will run like analytic app giving to the user the possibility to choose the supplier name.

Hope that help!

HomesickSurfer

Hi @Tommy_Alterax

Use: GetWord([Supplier name], 0)

This will work and more so when your suppliers change, instead of find/replace tool or REGEX_Replace formula.

Tommy_Alterax

This is great thank you !!

Tommy_Alterax

Hi Marcus - thanks for taking the time to respond - More than filtering an isolating these particular strings I am loking to clean them up into 1 consistent name - The formulae above is perfect for this - Thanks for taking the time

Tommy_Alterax

Hi Homesick,

Just wondering about your solution - What I want to do is clean my data into one consistent supplier i.e 'Amazon' and discard the remaining junk characters - The formulae tool 'Contains' works perfect as I can change the output if it contains 'Amazon'

I am unsure how Get Word will help me ?

Tommy_Alterax

Hi Emil,

Another final variation on this solution you gave me

In the sample excel file attached - I am only concerned with the Resource Name equal to 'Labor Vendor 0000025308,' ignore the remaining resource name. With the Original Name Column I am concerned with the 'John Resourcing ....' only when the resource name is 'Labor Vendor 0000025308,'

Bsically I want to an IF statement 2 variables with contains and replace , if it does contain

If Resource Name equals 'Labor Vendor 0000025308,and Original Name contains (John Resourcing) /// Note I do not care about the Month after John Resourcing

Then Resource Name equals John Resourcing

Else Resource Name

End if

Thanks Tommy

Sample Data.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335