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!

Phoenix, AZ

Welcome to the Phoenix User Group

Click in the JOIN GROUP button to follow our news and attend our events!

SOLVED

String Functions - Returns a specified segment from a string

Georgealex
7 - Meteor

Issue - Need help in string function to be used

Sample data

Case -1  : 66-YY-ZZZZ-AAAAAA-123
Case -2 : MX-YY-AAAA-ZZZZZZ-596
Case -3 : 66|YYYYYYY|ZZZZZZZZ|AAAAAAAAA|1|12|11234|X

 

Requirement

I need to extract "AAAAAA" from Case-1 , "AAAA" from case-2 and "AAAAAAAA" from case-3. This is the mix of the data I have in my file

 

in other languages we can extract the 4th segment from case-1 after the '-' , 3rd segment from case-2 after '-' and in case 3, 4th segment after '|'.

 

How do i replicate it in Alteryx.

9 REPLIES 9
Treyson
13 - Pulsar
13 - Pulsar

Are you trying to extract any time that it says "A*" or are you looking for those specific spots in your string and want to extract whatever is there?

Treyson Marks
Senior Analytics Engineer
Georgealex
7 - Meteor

Clarification - it is not always 'A' . It is just the position. it would be a alphanumeric combination.

Treyson
13 - Pulsar
13 - Pulsar

I am sorry, but the other clarification I guess I would need is, is the position you need to find based on case number or are those just examples of record ids? Specifically, in any given string, how do I know if I need to look in the third or fourth spot?

Treyson Marks
Senior Analytics Engineer
Georgealex
7 - Meteor

That is a good question. Sorry it was not very clear

 

Case 1 and case 2 is when substring("string",1,2)  = '66' and (substring("string",3,1) = "-" OR substring("string",3,1) = "|")

 

Case 3 is when substring("string",1,2)  = 'MX'

Georgealex
7 - Meteor

update

That is a good question. Sorry it was not very clear

 

Case 1 and case 3 is when substring("string",1,2) = '66' and (substring("string",3,1) = "-" OR substring("string",3,1) = "|")

 

Case 2 is when substring("string",1,2) = 'MX'

Georgealex
7 - Meteor

I figured out a round about solution using a combination of string functions

 

Step 1 :Use ReplaceChar(x, y, z): to replace special char with spaces

Step2 : Use GetWord(String, n) to extract the right word from the above result after spaces

 

Treyson
13 - Pulsar
13 - Pulsar

okay so the quickest solution I could come up with is attached. I just parse everything and then you would build your own logic around selecting the record (segment) that you need. I imagine you can do this with REGEX as well, but I am still a little fuzzy on which segments you are needing to grab. Is that excel syntax that you posted?

Treyson Marks
Senior Analytics Engineer
Georgealex
7 - Meteor

Thank you Treyson. I have downloaded and will review.

riosjosh
7 - Meteor

@Georgealex an alternative to using the String Functions in Alteryx would be to use RegEx tool. Very powerful tool that can parse out specific strings based on patterns. The following regular expression should accomplish your goal. Although I will say it is a bit predicated on your prefixes. If it is just though 3 or only a handful this solution will work. If there are many more then I would suggest break down the issue into smaller chunks and branching out your logic through multiple parses. I think you would get a better result and easier to follow logic in the more complex cases. BUT assuming only the three prefixes then this will do just fine. I agree with @Treyson that Regex101.com is killer and key to helping you through using RegEx.

 

(?<=66-)\w+-\w+-(\w+)|(?<=66\|)\w+\|\w+\|(\w+)|(?<=MX-)\w+-(\w+)

 

I've also attached a simple workflow. Hope it helps.

Labels