Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

Alteryx Designer Desktop Discussions

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

RegEx Parse formula needed when more than 1 numeric field of same length exists

182129
7 - Meteor

I have the following sample data that I'm trying to parse a specific number field out of, but the problem is sometimes there's a different number of the same length of 7 digits that I don't want. I tried parsing by the 2 dashes, but then realized some of the valid data only has 1 dash. Is there some way to take into account that I only want to use data that contains "Need ARLO Number" but not include that phrase in the results?

 

Field3Desired Result
No_Cost_Center-Need_ARLO_Number--1036528-10474934_0_1689001_WW001036528
No_Cost_Center-Need_ARLO_Number--1036569-10474975_0_1689001_WW001036569
No_Cost_Center-Need_ARLO_Number-HDQ-1036687-10475093_0_1689001_WW001036687
No_Cost_Center-Need_ARLO_Number--1036687-10475093_0_1689001_WW001036687
No_Cost_Center_WW00-7409101null
No_Cost_Center_WW00-7301181null
No_Cost_Center_WW00-7409101--No_ARLO_Numbernull
No_Cost_Center-Need_ARLO_Number--1036503-10474909_0_1689001_WW001036503
6 REPLIES 6
ShankerV
17 - Castor

Hi @182129 

 

It can be achieved with the combination of Regex and Formula tool.

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @182129 

 

Please find the use case explained for your scenario.

 

Screenshot 2023-07-18 222259.jpg

 

Step 1: Input the data

 

Screenshot 2023-07-18 222259.jpg

 

Step 2: Regex 

 

Screenshot 2023-07-18 222259.jpg

 

Screenshot 2023-07-18 222259.jpg

 

Step 3:

 

Screenshot 2023-07-18 222259.jpg

Screenshot 2023-07-18 222259.jpg

 

Step 4: Select tool to remove the unwanted columns

 

Many thanks

Shanker V

182129
7 - Meteor

Now that I think about it, could it be put in the same Formula tool using a RegEx_Replace with an IF contains "Need_ARLO"?

ShankerV
17 - Castor

Hi @182129 

 

As usual, there are n number of ways we can achieve the result with the help of Alteryx.

 

I shared one of the way to reach the solution.

 

Glad that I brought creativity to your solution to use Regex_Replace.

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @182129 

 

Please use the below formula. Hope it helps!!!!

 

IF Contains([Field3],"Need_ARLO_Number")
THEN Trim(REGEX_Replace([Field3], "(.+)(-\d{7})(.+)", "$2"),"-")
ELSE null()
ENDIF

 

Screenshot 2023-07-18 223325.jpg

Many thanks

Shanker V

sparksun
11 - Bolide

My solution purely using Regex tool:

forum.jpg

Labels