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

Tool Mastery | RegEx

Rosaile
7 - Meteor

Good Afternoon,

 

I'm trying to parse data in a column that has somewhat of a pattern to it, but can be entered several different ways.  The important pieces of data from these lines are the 2 to 3 letter initials (MWC, NR, etc) and the "Q* Year" portions.  The rest of the data is not necessarily needed, but I was hoping i could just split all the data out into pieces.  If the data was uniform I think I could write something fairly easy, but with this being a large amount of data and the data being consistently out of order, possibly missing data (ex. Q_2017 missing number part of the qtr).  I wasn't sure if there was a way for the expression to look forward across the whole line and look back. etc.

 

The first line I can pull the date doing something like ^(\d*\W*\d*\W*\d*), but that doesn't work for line 2, 3, & 4.  Any suggestions?

 

Ex.

line 1:           11-21-16 MWC - Q1_2017 savings; $123,456

line 2:           MWC  6/10/18 - Q2 2018 savings; $123,456

line 3:           NR 5/1/2017- Q 2014 $123,456 savings.

line 4:           Q4_2010 $123,456 savings; CM 1-1-11

 

Thanks!

2 REPLIES 2
Thableaus
17 - Castor
17 - Castor

Hi @Rosaile 

 

Could you please try this out?

 

Formula1.PNG

 

Initials:

 

REGEX_Replace([Field1], ".*?([A-Z]{2,}).*", "$1", 0)

 

Q Year:

 

REGEX_Replace([Field1], ".*?(Q\d?.?\d{4}).*", "$1", 0)

 


Cheers,

Rosaile
7 - Meteor

Wow!  Thank you as that appears to have done exactly what I wanted for all the data.  I'm going to spend the next couple hours trying to reverse engineer what you did so I can understand it going forward.  Thank you very much.

Labels