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

How to extract unique IDs from a text field

hellyars
13 - Pulsar

 

I want to pull out references to unique IDs from a Text field.  The text field can vary in length.  The IDs have a specific construct:  (7- numbers + 1 letter) or ( 7-numbers + a combination of 1-3 letters and numbers).  Here are a few examples (e.g., 0601102A, 0601102BR or 0601102D8Z) .  ***The ID always starts with a 0 or 1.***.  

 

How can I extract these into a new field that list the 1-n references separated by a comma.  Example below...

 

Text Field to SearchNew Desired Output Field

"Lorem ipsum dolor sit amet, suspendisse sodales vitae quisque urna vestibulum aliquip. Nec elit eget ornare suscipit, viverra fermentum posuere sit, porttitor dictum justo duis ut, id amet ut magnis pede. Sodales sollicitudin odio rerum erat, eros in, maecenas ac facere bibendum pede pede, metus sollicitudin justo volutpat nibh, voluptate at tellus donec nam pulvinar faucibus. 

 

Work in this project builds on the materials research transitioned from PE 0601102A and 0601104A. This work complements and is fully coordinated with PE 0602618A

(blah blah), PE 0602786A (blah blah), and PE 0603001A (blah blah).

 

Eu eget congue quis pretium aenean eget, hendrerit nulla, ultrices sed volutpat sed elit vitae ornare, occaecat ut. Lectus quisque nullam. Tellus semper bibendum ante lorem."

0601102A,0601104A, 0602786A,0603001A

 

 

 

 

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hey @hellyars

 

Where's this text field coming from?

Which kind of input data are you using?

 

Cheers,

hellyars
13 - Pulsar

Excel. It's just a text field. 

Thableaus
17 - Castor
17 - Castor

@hellyars

 

Here's the solution I thought.

 

Solution2.PNG

 

Although there are many ways to do this. Let me know if there are any doubts about it.

 

Cheers,

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

I'd suggest using a Regex tool in tokenise mode. You can use a summarise tool to concatenate the results;

2018-11-03_20-53-41.png

 

Sample attached

hellyars
13 - Pulsar

Sorry, I thought I responded.  

 

This works perfectly.  I was worried that applying it to my real dataset would bog down the processing time, but it did not.

 

Many thanks.

hellyars
13 - Pulsar

This also works.  It is more refined.  And, it seems to address my concern that the parse approach could impact workflow performance when working with my full dataset.

 

Thank you. 

Labels