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

Parse Member ID from Multi Line Text Field

RifferX
8 - Asteroid

Hi all,

 

I've tried going at this a few different ways with no luck. I am pretty raw yet with Regex and am hoping this current business need will spring my skillset further with some help from the Community here. I have also tried Text to Columns with no luck. 

 

I need to parse out the Member ID in the dummy example string below into a separate column. Whatever method that can locate and extract it I am open to, whether it's via Regex or other methods. The Member ID can be different lengths, but there is consistency with the line it is contained in and the 4 spaces prior to the number after the line text, which is "TAT Goal: 5 Days Member Details :    2931317250"

 

Here is what the Comments field looks like and I have also attached a screenshot with the desired output. I have about 26,000 rows in my dataset. Any help on this would be appreciated immensely and I am not concerned about what happens to the original Comments field. *Cannot post the text here without the system picking it up as spam, so attaching via Notepad.

15 REPLIES 15
RifferX
8 - Asteroid

@atcodedog05 let me dummy something up. I have 2 scenarios. One where the Provider info is populated and one where it is not. Be right back.

RifferX
8 - Asteroid

@atcodedog05 First example below is how the text field appears in a cell and it has Provider Info. The second example does not have Provider info, so I'd want to return a blank for both PROVIDER_TIN and PROVIDER_NAME.

 

First example

RifferX_0-1660241693339.png

Output columns should be:

PROVIDER_TIN

390908226

&

PROVIDER_NAME

BEST WESTERN HEALTH

 

Second example below:

RifferX_1-1660241920795.png

Need to return blanks:

PROVIDER_TIN

 

&

PROVIDER_NAME

 

 

 

I am close with what I'm doing now using 2 separate Regex parse tools, but when the Provider info is blank, I am getting a result like --

PROVIDER_TIN            .*Provider Details :\D+(\d+).*

1096148 (this is a truncated number below in the cell without the leading letters)

PROVIDER_NAME       .*Provider Details :([^\d]+).*

Matched TAT rule!

atcodedog05
22 - Nova
22 - Nova

Hi @RifferX 

 

Can you please provide this sample data in excel so that will be easy for me to build a workflow with and help you out.

 

It probably might be escape newlines and it might work.

 

 

PROVIDER_TIN            .*Provider Details :[^\n\d]*(\d*).*

 

PROVIDER_NAME       .*Provider Details :([^\d\n]*).*

 

Hope this helps : )

RifferX
8 - Asteroid

@atcodedog05 Your solution appeared to work but I haven't tested it entirely yet. Sorry, I had a meeting, 

atcodedog05
22 - Nova
22 - Nova

Awesome🙂!! I hope you also understood how it works. 

 

It's night here I will be logging off for the day. If any more questions, I would probably answer them tomorrow.

 

Hope this helps : )

RifferX
8 - Asteroid

Thanks @atcodedog05 

I will wait on your response when you get a chance. Really appreciate your help with this as it was a deliverable today. Cheers!

Labels