Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

RegEx Problem

Highlighted
8 - Asteroid

Hello everyone,

I'm trying to get a part of information out of the file path

File path is like this 

blablablablabal\A12_2015.xlsx|||`01_2015$`

want to get this part

01_2015

 so my Regex is 

\`\d{2}\_\d{4}\$\`

but it's not working and it won't give me the resualt that i want (It's returns NULL)

 

How can i achieve this?

Highlighted
14 - Magnetar
14 - Magnetar

Hi @MostafaBouzari 

 

Which RegEx tool are you using? Regex parse or Regex functions in a formula tool?

 

In either case, to pull something out of an expression, you should mark what you want returned in parentheses.

 

Alternatively, you can try the FileGetFileName function to pull out most of the string, and then cleanse out the alpha characters from the field.

 

Let me know if that works.

 

Cheers!

Esther

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @MostafaBouzari 

 

if you're looking to return on the sheet name from that path, the string functions offer a non-RegEx alternative.

 

Trim(Substring([FullPath],FindString([FullPath],"|")),"|$`")

 

The Substring( function will return the remainder of the string that occurs after the "|" character, which leaves "|||'01_2015$'". This can then be wrapped with a Trim( function to remove the unwanted characters ("|$`").

 

Check out the attached workflow to see this in action.

Highlighted
12 - Quasar

@MostafaBouzari ,

You can try online  before using RegEx in Alteryx .

https://regex101.com/r/Yxxte9/1

Highlighted
17 - Castor
17 - Castor

Hi @MostafaBouzari 

 

The Alteryx Regex parser seems to be having problems with \`.  If you remove the escapes from before both apostrophes, the expression works. 

 

 

`(\d{2}_\d{4})\$`

 

 

Include the capturing group as @EstherB47 suggested, of course.

 

It's strange because \' works on Regex101.com but not in Alteryx.

 

Dan 

Highlighted
14 - Magnetar
14 - Magnetar
The discrepancy with RegEx101 could be the Perl regex, as opposed to other
flavors.

Great catch, @danilang!!

--
Esther Bezborodko
*Senior Manager*
201.650.7314 | estherbezborodko@gmail.com
beautycounter.com/estherbezborodko

*Our mission is to get safe products in the hands of everyone.*
[image: Facebook]
Highlighted
8 - Asteroid

Thank you so much @EstherB47 

Didn't know that i should use parenthesis 

but i have another problem regarding checking if this is the correct RegEx,Cause it works in RegEx101 but not in alteryx

 

E.g: alteryx seems to not recognize \´  as @danilang  mentioned 

 

Is there sth that we should consider when we want to test a Regex in regex101.com?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hmmm, is this a regex challenge?

 

FileGetExt(Path): Returns the extension of the path, including the . (period).

 

Replace(ReplaceChar(FileGetExt([FileName]),"'.|",''),"xlsx",'')

 

i didn't test this reply but think that it will run 3x as fast as the regex. 

cheers,

 

 Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
17 - Castor
17 - Castor

Hi All

 

As @MarqueeCrew said, 

i didn't test this reply but think that it will run 3x as fast as the regex. 

I've always thought this as well, because of the complex way that Regex scans and parses strings.  So I did test it and the results are interesting.  1M, 10M and 100M rows using the methods from @MarqueeCrew(FileGetExt), @CharlieS(Substring) and myself(Regex)

 

r.png

As Mark conjectured, the native string functions used in Charlie's solution do run ~3 times faster than my Regex based solution.  But what's happening with Mark's method.  It's avoiding Regex, using the FileGetExt() function, but it's run time is on par with the Regex sol'n and 3 times slower than the String based formula.   Strange!  Of course, this could be explained if FileGetExt() uses Regex internally to perform its magic.

 

Dan

 

 

 

 

 

Labels