Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here

Split a text at every "AND" using text to columns

Highlighted
6 - Meteoroid

My requirement is as below:-

1.I want to split a text at every AND and put it into new columns.

2.But the number of ANDs might vary.For example:

One data point in the given column would have Name AND School AND City AND Country.and i would want 4 columns created for Name,School,City,Country.

But another data point might have Name AND School AND Country.Here i would want the same columns but with Blank in the city column.

 

Sample data:-

ContatinatedStringColumn

1.Name contains('Tom') AND School contains ('ABC High School') AND City Contains('Vancouver','Toronto') AND Country Contains ('Canada')

2.Name contains('Adam') AND School contains ('ABC High School') AND Country Contains ('Canada')

 

How do i do this?Any help would be appreciated.

 

Highlighted
13 - Pulsar

Hi @arlok2005,

 

The key is not the parsing, but how to identify which data point belongs in each column.  Think of teaching a person how to do this if that person had no context of city or country.  Absent a key (i.e. - Text Box with list of Countries) or training (machine learning), it becomes almost impossible for that person to be completely correct.  Even then there is a possibility for error where a city is named after a country (Singapore, MI).  Are there any other identifiers in the data?  Sample data may help.

Highlighted
6 - Meteoroid

Ive amended the question to have sample data.

Am pasting the sample data as a response to you aswell:

 

Sample data:-

ContatinatedStringColumn

1.Name contains('Tom') AND School contains ('ABC High School') AND City Contains('Vancouver','Toronto') AND Country Contains ('Canada')

2.Name contains('Adam') AND School contains ('ABC High School') AND Country Contains ('Canada')

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @arlok2005, one way to build this solution could be to use Text-to-Column in conjunction with Tile tool to compute your output. I have included a sample workflow that you could build upon.

AbhilashR_0-1595269102640.png

Let us know if this isn't what you are looking for. 

Highlighted
12 - Quasar

I had a go. The trick will be knowing which value is missing.

 

wwatson_0-1595269131189.png

 

Highlighted
12 - Quasar

Another Version

wwatson_0-1595269709348.png

 

Highlighted
13 - Pulsar

Another option that matches your data

 

Split on AND.png

 

Highlighted
Alteryx Partner

Did the whole thing with as single RegEx expression.

RegEX.png

 

RegEX results.png

Labels