Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

REGEX_Match function to match subset

kavithabanda
7 - Meteor

Hello!

 

I'm trying to resolve the following case:

 

I have to find a match in a list and the example is:

 

List of words:

------------------

PEPPERCORN

CORN MEAL

CORN OIL

CORN

 

The pattern match word is "CORN" so my result should return CORN MEAL, CORN OIL, CORN but NOT PEPPERCORN.

 

I built a sample workflow that uses both REGEX tool and a formula that uses REGEX_Match. REGEX tool returns correct results but I am not sure how to pass a dynamic string there instead of hard coding my pattern.

 

REGEX_Match does not match CORN MEAL, CORN OIL. It matches CORN with CORN only. How can I make this function work for 

CORN MEAL, CORN OIL (but not PEPPERCORN)?

 

I'm more leaning towards using REGEX_Match solution.

 

I'm attaching my workflow.

 

Thanks in advance!

K.

 

11 REPLIES 11
ddiesel
13 - Pulsar
13 - Pulsar

Hi @kavithabanda

 

Do you have a list of words that you're trying to match? If so, I think a Find Replace tool will work for you. In the configuration, choose "Any Part of Field" and check the box for "Match Whole Word Only". You can make case sensitive/insensitive depending on your data. Then choose "Append Field(s) to Record:" instead of "Replace". I added "Peanuts" as an example.

 

Capture.PNG

 

See attached and let me know if this works!

 

 

kavithabanda
7 - Meteor

Thanks ddiesel, looks like this solution might work for me. I have to put this back into my original gigantic workflow to see how it plays there but this is what I was after. 

kavithabanda
7 - Meteor

@ddiesel,

 

This solution works separately but isn't working when I inject into the larger workflow. I am back to looking a solution using REGEX_Match. Any ideas?

derekbelyea
12 - Quasar

 

Have you tried using the FUZZY MATCH tool?

kavithabanda
7 - Meteor

No, Let me check that out.

ddiesel
13 - Pulsar
13 - Pulsar

Hi @kavithabanda 

 

If Find Replace and Fuzzy Match don't work, can you provide a larger sample of the data you're trying to match? What's not working about it?

 

There is a solution somewhere!

ddiesel
13 - Pulsar
13 - Pulsar

@kavithabanda

 

Check out this post:

Find-exact-matches-from-multiple-fields-using-batch-macro

 

@knozawa wrote a macro that I think solves for your use case.

 

Let us know if it works for you!

 

 

Fruitlord
5 - Atom

Instead of "Corn" ... Try "Corn.*" or ".*Corn" depending on whether your string normally starts with corn

Saumil
5 - Atom

Hi @ddiesel:

 

I am trying to do the exact same thing that you did using Find Replace tool. I have a list of words that I am trying to match from a column of text descriptions. The only problem that I am facing is that if the text contains more than one word from the list of words I want to match, it only returns the first one in the match column and not the remaining. 

 

For example: If I have a field with text "CORN PEANUT BUTTER" I want to return CORN and PEANUT both as output in the matched column and not just CORN. Is there a way I could do this ? Thanks in advance.

Labels