Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

using REGEX to loop through a spreadsheet?

Highlighted
6 - Meteoroid

I'm new to Alteryx and I'm trying to finish off a workflow which will replace misspelled words using a Excel sheet:

srea541_0-1581412251183.png

 

This table is joined to another table to look like this:

srea541_1-1581414681755.png

 

The formula I am currently using is:

 

REGEX_Replace([_CurrentField_],[Wrong Spelling],[Right Spelling])

 

However, this formula only replaces all instances of the word 'cant' in row one, and does not run through all the rows. I'm not sure where to go from here. 

This is what I get:

 

srea541_2-1581415394114.png

 

'Im' needs to be replaced as well but is not.

 

Any help would be greatly appreciated!

 

Highlighted
Alteryx Certified Partner

Hi @srea541,

 

Rather than using regex expressions to achieve this could you use a simple find and replace tool? I'll put together a sample workbook for you.

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Jonathan

Highlighted
Alteryx Certified Partner

Hi @srea541,

 

So as i said you could use a find and replace tool to look up misspelled words and replace with the correct spelling:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Highlighted
6 - Meteoroid

Hi Jonathan,

I have a solution that uses find and replace:

srea541_1-1581417669756.png

The problem is I have to find and replace the words in each column individually, which works but is very time consuming. For the project I'm working on, I would need to do this 24 times to achieve what I need.

 

Stephen

Highlighted
Alteryx Certified Partner

Hi @srea541,

 

So in this case i would transpose your data before the find and replace tool, i'll mock up a sample.

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan

Highlighted
Alteryx Certified Partner

Hi @srea541,

 

Please see the attached:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Highlighted
Alteryx Certified Partner

However @srea541 if you want to be 100% accurate i would go down the route of tokenising each word and then using the find and replace technique. This will stop any issues of finding a word in a word, for example "estimate" you would find "im" and become "estI'mate".

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Highlighted
Alteryx Certified Partner

And finally using the field info and dynamic rename tools to ensure the column headers exactly the same as before the crosstab:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Highlighted
6 - Meteoroid

Hi Jonathan,

I think we're getting closer!

srea541_0-1581419786323.png

 

The issue is now on the last step, it is sticking all of them into one row:

srea541_1-1581419966439.png

 

Another thing I noticed was that the find and replace function removes the '.' from the end of the fields as well as any occurences in the middle of the string. These would need to stay in.

 

Stephen

 

 

Highlighted
Alteryx Certified Partner

Hi Stephen,

 

In that case how about this:

 

image.png

 

I'm not sure how you want the output structured? As you've said you don't want it all on one row?

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Labels