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

REG_EX Match formula vs REG_EX Parse tool

adamp
7 - Meteor

I've got a fairly simple regex statement and I'm intending to use it to filter some data. I want to separate rows based on whether or not a text field contains two numbers followed by an apostrophe (e.g., 40')

 

I'm using the regular expression \d{2}' and it test just fine. It also works when I put it into the RegEx Parse tool... I can see a new variable with the expected values from the original string.

 

But, when I put that same regular expression into a formula on a filter tool it only returns nulls: (REGEX_Match([Material Description],"\d{2}'")

 

Can someone enlighten me about what's going on here and if/how I might get this working as a formula in the filter tool to avoid adding additional tools to my workflow?

9 REPLIES 9
PaulN
Alteryx Alumni (Retired)

Hi @adamp,

 

Thank you for posting!

I can't seem to be able to recreate your issue. Could you please check my example below and let me know what is different in your case ?

 

test_regex.png

 

Kind regards,

 

Paul Noirel

Sr Customer Support Engineer, Alteryx

 

adamp
7 - Meteor

@PaulN Your test looks like what I'm doing and only the results are different. I've attached the offending part of the workflow for you as well as a screen shot.

 

REGEX-MATCHing.png

 

 

 

PaulN
Alteryx Alumni (Retired)

Hey @adamp,

 

Many thanks for the clarification!

 

So the result is expected:

 

In my case I have configured RegEx tool with output method set to Match which is the equivalent of REGEX_Match.

 

regex_configuration.png

 

On your screenshot, I can see that the tool is set to Parse so it's going to use the regex to extract any matching pattern. This explains why "1" will not return any results while "10' DELUXE DOORS" actually returns "10'" which is a match for \d{2}'.

 

I hope this helps.

 

Thanks,

 

Paul Noirel

Sr Customer Support Engineer, Alteryx

 

 

adamp
7 - Meteor

@PaulN What I'm really looking for is understanding why the REGEX_MATCH in the filter isn't finding anything. I only setup the Parse as a check on my regular expression. I understand that the Parse configuration is slightly different and putting the parenthesis changes the regular expression slightly. Any thoughts on why \d{2}' isn't matching anything in the filter tool?

Nate1
8 - Asteroid

Hi @adamp

 

Your filter tool doesn't have a condition in it. You need to add an = to the filter. Here is some update regex that accounts for the possibility of text before or after the match you are looking for.

 

REGEX_Match([Material Description],".*\d{2}'.*") = -1

 

PaulN
Alteryx Alumni (Retired)

Hi @adamp,

 

The reason of the behaviour, as shown in @Nate1 example, is because your regex will only capture patterns like "10'" but not "10' DELUXE DOORS". You could see your expression as "^\d{2}'$" if you prefer. If you expect characters before or after, you will need to use the following expression :

 

REGEX_Match([test], ".*\d{2}'.*")

Note that as REGEX_Match() already returns a boolean, there is no need of a operator like "=" or "!=".

 

Thanks and thanks to @Nate1,

 

Paul Noirel

Sr Customer Support Engineer, Alteryx

 

 

adamp
7 - Meteor

I knew it was probably something simple! Thanks. @PaulN @Nate1

 

I guess instinctively I expected the Match function to already expect and/or ignore superfluous items in the string and just look for the token I specify. One day I'll be practiced enough (I hope) to remember ~ I ~ have to account for the full string in addition to the token I'm looking for.

hannahhanigan
5 - Atom

I'm having a similar issue. I have a set of numbers, some of which have leading and trailing numbers that I need removed. Basically, if the item number column begins with a 6, I want to remove the first two numbers and the last 1 number, but if the number begins with anything else, I want to leave it alone. I'm pretty confident that I need to use the RegEx tool, but not sure how to do an "IF" type function in association with doing a fixed width. 

 

image.png

PaulN
Alteryx Alumni (Retired)

Hi @hannahhanigan,

 

If I understand your case correctly, a simple formula with text functions will do the trick:

 

if Left(ToString([Item Number]),1) == "6" then
   ToNumber(Substring(ToString([Item Number]),3,Length(ToString([Item Number]))-4))
else
   [Item Number]
endif

 

I have assumed that "Item Number" was a numeric, hence multiple ToString() functions. 

To sum up:

if first character is "6" then return a sub string starting at the 3rd character and of a length of original item number minus 1 character and minus the original position.

 

EXCEL_2019-06-16_21-43-21.png

 

Thanks,

 

Paul Noirel

Sr Customer Support Engineer, Alteryx

Labels