Alteryx Cleaning Data

[Tommy_Alterax]

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

[Emil_Kos · Accepted solution]

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!

[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 ? 

[Emil_Kos]

Hi @Tommy_Alterax,

Thank you for marking my post as a solution. Good luck!

[HomesickSurfer]

Hi @Tommy_Alterax 

The use of GetWord([Supplier name], 0) will return the first work in the string, omitting the extra trailing words and such in the string.  If your list of suppliers remains as 'Amazon' each run and only 'Amazon, then use of 'contains' will suffice.  If otherwise, you will need an interface question to specify the supplier name.  GetWord([Supplier name], 0) offers a dynamic solution.

[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