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

Remove data from several parenthesis in string

edasilva80
5 - Atom

Hi!

 

I am trying to find a way to extract the data withing each parentheses into different columns 

 

For Example 

XXXXXXXXX (NJ, USA)   - Multiline (4216137)

 

XXXXX, LLC (USA)  (4065310)

XXXXXX (CA, USA)  (100.00% owned) - Research and Consulting Services (6458042)

 

 

 

There can be a different number of parentheses within the row (up to 5). I would like my output to look like this:

 

XXXXXXXXX  NJ, USA 4216137
XXXXX, LLC USA  4065310
XXXXXX CA, USA100.00% owned6458042

 

Thank you very much for your help!

 

Edgar

4 REPLIES 4
Inactive User
Not applicable

Answer attached.

danrh
13 - Pulsar

I wanted to figure this out using Regex :)

image.png

 

The idea here is to parse the data to rows based on the parenthesis, number each occurrence within the string but forcing the final occurrence to equal 5 (per your requirements of "up to 5"), Cross Tab back to rows, and join back to the original data.  The Record ID maintains organization, both for the Cross Tab and the Join tools.

 

Interesting challenge.  Hope this helps!

 

EDIT: Glanced at this again and saw that you wanted the original data to show just what was prior to the first parenthesis.  Added another Regex tool to accommodate.

EmanueleE
8 - Asteroid

hi @edasilva80,

 

here another way to solve your problem.

 

You have to use a regex tool setted on Parse Method and use as Regular Expression (see img1):

 

img 1img 1 

.+(\(.+\)).+(\(.+\))

 

and delete with a replace or with a left(right) method (just let me explain) the brackets.

 

All the three flow that i created have a text tool, a regex tool, the difference is the last part that delete the brackets in different ways (img3).

 

img 3img 3

 

- difficult way ad a multi field for change all type (maybe you can use a select tool),

the first multi field delete the first bracket and the second the remaining bracket into the output from the regex (img2).

 

img 2img 2

- left(right) method use a formula that delete   with this formula the brackets

 

IF CONTAINS([_CurrentFieldName_],'RegexOut')

then right(left([_CurrentField_],length([_CurrentField_])-1),length(left([_CurrentField_],length([_CurrentField_])-1))-1)
else [_CurrentField_] endif

 

- simple way use a formula tool which have 4 formula that delete 1 bracket per formula for 2 column

 

I like the left(right) method, because it finds automatically the new columns and delete with 1 tool the brackets (if in the future you had another information within '( )' the flow automatically convert it)

 

PS use a reegx test to help you with the parse

 

https://regex101.com/

 

 

EmanueleE
8 - Asteroid

adding another solution

 

formula tool for copy the field,

regex to tokenize the text \(([^)]+)\)

multi row to dispose them in the right place 

transpose tool to rotate them

 

solutionsolution

regexregex

multirowmultirow

 

Labels