Extract Multiple Text from a String in to New Column and Then Use That Column to Match
- RSS-Feed abonnieren
- Thema als neu kennzeichnen
- Thema als gelesen kennzeichnen
- Diesen Thema für aktuellen Benutzer floaten
- Lesezeichen
- Abonnieren
- Stummschalten
- Drucker-Anzeigeseite
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
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 |
Gelöst! Gehe zu Lösung.
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
@JPSeagull
I use the comma as delimiter to break the columns in to several rows. then perform a RegEx parse. hope this works
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
Hi, @JPSeagull
Maybe this is your want output?
RegEx Expression:
\,{0,1}[A-Z]*(?=\s-\s[A-Z]{1,}\s)
Add the compare view:
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
@JPSeagull Another way of doing this, similar to @Qiu method. @flying008 nicely done with less tools!!
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
@flying008 - amazing! Especially think the animated demonstration helped towards my final solution! Thank you!
@Qiu Thank you! It really helped to get the final solution as I used the combination of all three inputs.
@binuacs - Thank you for including the second input for the assigning priority. I really appreciate the time.
With the help of you three, I was able to see what I needed to do to separate into additional rows, by Record ID, do the lookup of priority with Find and Replace and regroup them via Summary. My final version is attached. Again - than you all!
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
Good to know it works. Thanks.