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 search a field for a specific string and return what follows after the colon

newuser980345483
7 - Meteor

Hello, 

 

I have a text field that contains multiple details (example below). I would like to query this field for the string "Item / Art" and return what follows after the semi-colon. It should return every instance of this. It will vary how many times the string "Item / Art" appears in the field. 

 

See field value below. It should return the following in one field separated by a comma: 1234567, 2345678, 9876543

 

FIELD VALUE

Description / Description : PRODUCT 123

Item / Art. : 1234567

UPC / CUP : 12345678912

Retail / P. det. : $1.87

Size / Format : 181g

 

Description / Description : PRODUCT 1234

Item / Art. : 2345678

UPC / CUP : 12345678912

Retail / P. det. :$3.41

Size / Format : 78g

 

Description / Description :PRODCT 789

Item / Art. :9876543

UPC / CUP :098765432109

Retail / P. det. :$1.98

Size / Format : 213g

 

 

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @newuser980345483 

 

Here's a solution with no Regex:

solution_noregex.PNG

 

- Filter Item/Art records

- Use Text to Columns to separate the colon 

- Use Formula Tool to trim the records

- Use Summarize tool to concatenate your values

 

WF attached on version 2018.4

 

Cheers,

ivoller
12 - Quasar

Just for the sake of it - a solution with Regex

 

2019-05-28_17-55-56.jpg

newuser980345483
7 - Meteor

Hello - thanks for your response! 

 

I realize my initial question wasn't as clear as it could be. Please see attached. The field value I provided is one cell value. There can/will be multiple cells with similar structure, but could have any number of descriptions / item numbers, etc. within. I have attached this for clarity. 

 

The output would ideally return the following:

IDItem / Art
123

1234567, 2345678, 9876543

234

8765421, 8520369, 0658941, 3579510

345

3456789

885

1598745, 8521479

Thableaus
17 - Castor
17 - Castor

@newuser980345483 

 

Just with a few changes we can have your final output:

 

Changes.PNG

 

- Add a Text to Columns Tool before the filter - Use the /n delimiter and split to rows

- In the Summarize tool, group by ID

 

WF attached.

 

Cheers,

newuser980345483
7 - Meteor

Based on both responses provided, I gave this a bit of a try and came up with the attached solution. 

Thableaus
17 - Castor
17 - Castor

@newuser980345483 

 

No problem, just keep in mind that the my last post gets to exactly what you need.

 

Cheers,

Labels