Hi. I have encountered something that I cannot find solutions. Attached is an Alteryx file with two text inputs tools. The first text input is a single column of services that is from raw data created from an ancient reporting system. For the most part, I can do a LEFT([Services],FindString([Services], ' - ')) which will get me all of the single items in this column. However, there are instances where the service entry appears more than one time separated by ", ," (comma, space, comma). It can be two, three or more, but the pattern is the same: services are separated by ", ,". I need to figure out a way, after using the LEFT function for the only (or first) service listed to search and extract only the part of the string after the second comma and up to the " -" (space hyphen) until the string is exhausted.
I have tried to find support topics close to my example, but struggled with using REG_EX where it keeps coming up as best way to attack the problem. In the table below, the second column is my desired output which will often include duplicated values of which I do need.
Once I can extract all the strings from the condition, I then need to look up the priority of that service. I assume Find and Replace tool is the best option? Will this work if the are more than one service with different priorities? Will I get all the priorities or just the first match? Thanks for the input.
| Services (Current View) | Services (Desired View) |
| AUDMER - FIX - 09/19/2008, ,MER - FIX - 09/19/2008, | AUDMER , MER |
| AEM - FIX - 01/28/2002, ,DYH - ADDITIONAL - Pending, ,INJMSI - ADDITIONAL - 02/18/2002, | AEM , DYH, INJMSI |
| GSI - EXISTING - 07/22/2000, | GSI |
| GSI - FIX - 08/03/2000, ,GVI - END - 11/02/2000, | GSI , GVI |
| INJMSI - FIX - 06/24/2002, ,INJMSI - FIX - Pending, ,INJMSI - END - 06/12/2000, | INJMSI , INJMSI, INJMSI |
| IPO - END - 11/13/2000, ,PAT - END - Pending, | IPO , PAT |
| MER - EXISTING - 10/21/2000, | MER |
