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 remove Prefix & Suffix from the raw data

Rajeev18
8 - Asteroid

Hello Team,

I want to remove Prefix & Suffix from the raw data. Output data should data after removing it. In this example i want product id withwithout collon data

 

Input Data is like below: Prefix & removal to apply on Product ID.

 

Product IDProduct Name1st level Approval statusDate
17I:634534:V9CWIP2019-10-29
17I:3019IA:V2WSubmitted2019-12-11
18I:423123:V1sAccepted2019-12-13
18I:1323451:V2qIn Transmit2020-01-10
19I:98765:V3rIn Transmit2020-01-16
19I:234567:V1eSubmitted2019-12-11
19I:234987:V4uIn Transmit2019-10-27
343011_2018WSubmitted2019-12-11
12345-2019sAccepted2019-12-13

 

Output Data required like below:-

Product IDProduct Name1st level Approval statusDate
634534CWIP2019-10-29
3019IAWSubmitted2019-12-11
423123sAccepted2019-12-13
1323451qIn Transmit2020-01-10
98765rIn Transmit2020-01-16
234567eSubmitted2019-12-11
234987uIn Transmit2019-10-27
343011WSubmitted2019-12-11
12345sAccepted2019-12-13



6 REPLIES 6
AbhilashR
15 - Aurora
15 - Aurora

Hi @Rajeev18, you could use the regex tool and parse the product ID field.

 

The Regular Expression in this case would be:     \:(.*?)\:

1.PNG

 

Hope this helps!

 

afv2688
16 - Nebula
16 - Nebula

Hello @Rajeev18 ,

 

would this help?

 

Untitled.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Rajeev18
8 - Asteroid

Wit this only the punctuations will be removed. I want along with punctuations number and letter which are supporting that punctuations also to be removed. 

Like 
if the data is this way - 19|:634534:V1  -- - Output data required - 634534.


afv2688
16 - Nebula
16 - Nebula

Are you talking about my solution? It removes all the required suffixes and prefixes

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

AbhilashR
15 - Aurora
15 - Aurora

@Rajeev18 - I initially didnt pay attention to the different combinations of Product ID you had, sorry. Take a look at the attached file and see if this suffices your request.

 

My approach is basically looking to identify different combinations of Product ID you have, and find the right regular expression to parse the data. Once I identified the string, I used a formula tool downstream to unify the product ID list.

 

For your case, the expression I used is  \:(.*?)\:|^(.+?)-|^(.+?)_

 \:(.*?)\: helps parse ID that look like 17I:634534:V9

^(.+?)- helps parse ID that look like 12345-2019

^(.+?)_ helps parse ID that look like 343011_2018

 

Hope this helps!

Simha
9 - Comet

Please see attached solution using RegEx. You can further modify according to your requirements.

Labels