community
cancel
Showing results for 
Search instead for 
Did you mean: 

Phoenix, AZ

Welcome to the Phoenix, AZ User Group! Please join us in sharing experiences and knowledge. Phoenix User Group Leaders
Welcome & Guidelines

Find tips to make the most of your User Group experience.

LEARN MORE
Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Highlighted

String Functions - Returns a specified segment from a string

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
Quasar
Quasar

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?

Meteor

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

Quasar
Quasar

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?

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'

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'

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

 

Quasar
Quasar

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?

Meteor

Thank you Treyson. I have downloaded and will review.

Meteoroid

@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.

Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.