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

Using RegEx to extract part of a string based on another field value

ansonwun
8 - Asteroid

Hi, I'd like to extract part of a string based on the value of a field value but got stuck with RegEx. My data is like this: the "Name" column lists out all the individual's name, but it may not be unique. The "Position" column lists the name and position of everyone - so it's the same every row.

 

NamePosition
Doe, J.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Tan, T.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
West, P.Y.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Stan, A.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Doe, J.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ

 

What I wanted to do is get this result:

NamePosition
Doe, J.Doe, J., Manager, Operations
Tan, T.Tan, T., Director
West, P.Y.West, P.Y., Clerk, Sales; HQ
Stan, A.Stan, A., Sales, Global
Doe, J.Doe, J., Intern, HQ

 

So far I've only used RegEx with some actual values instead of referring to a field combined with RegEx syntax so I'm not quite sure how to approach this. 2 other complications is that Names could be the same but Position can be different (e.g. 1st vs last row are actually 2 different persons), and there will be user input error putting the delimiter at the wrong place (e.g. the 3rd row, "Sales; HQ" instead of "Sales, HQ")

 

Thanks in advance!

2 REPLIES 2
BenMoss
ACE Emeritus
ACE Emeritus

This gets complex primarily because of the ; HQ; error.

 

I've built a rule that splits the data to rows on the ; delimiter. Then used the multi row formula tool to pull up the data from the following line if there is no comma within it.

 

See the attached solution.

Ben

ansonwun
8 - Asteroid

Thanks @BenMoss 

Inspired by your solution I've replaced every ": " + [Name] pattern with a delimiter other than ";" so that will take care of that unexpected delimiter inside the string and got what I was looking for.

Labels