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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Need to trim information

Asteroid

Hi there...I have this data in my file:

EDUC-6733-11/EDUC-6733J-11 I need it to look like this:

EDUC-6733/EDUC-6733J

 

I also have some that need to end up like this:

NRSE-6051/NURS-6051/NURS-5051

 

Essentially, I need to remove the course section (the section is the number 11 after the second hyphen). I've tried a number of different formulas but I either end up removing the second course or end up with the section.

 

I have this formula:

Substring([Course],0,4)+'-'+Substring([Course],4,4)+'/'+Substring([Course],0,4)+'-'+Substring([Course],4,4)

BUT, it does not capture the 5th item (6733J).

 

The formula above isn't working.

 

Meteor

HI @KAFord

 

This formula should work

 

Substring([Course],0,9)+'/'+Substring([Course],15,8)

 

 

Let me know if you can get this.

 

Thanks,
Ramana

Asteroid

Thanks...I had to make an adjustment so the formula looks like this now:

 

Substring([Course],0,9)+'/'+Substring([Course],12,9) which works but if there are more than 2 courses in the crosslist, say three or four courses, then it only gives me the first two courses.

 

I need to be able to trim this:

 

NRSE-6051-11/NURS-6051-11/NURS-5051-11

 or

 

FPSY-8720-2/APSY-8401-2/FPSY-6720-2/PSYC-8401-2

 

to this:

 

FPSY-8720/APSY-8401/FPSY-6720/PSYC-8401

 

and if there is a single course, it is giving me a '/' behind it like this:

 

PUBH-1000/

Alteryx Certified Partner

Hi @KAFord,

 

What i would do is use regex to get it done, as you have a combination of letters, a hyphen and then letters (ABCD-Y1122). You just need to tokenize your field and split to rows using this regex : (\w+-\w+).

Then use summarize tool to concatenate it using "/" separator. You must have a identifier field to group and concatenae. Incase you don't have then you can use recordid tool.

 

regex.JPG

Asteroid

Thanks, Vishwa...that did the trick. I don't know why I didn't think of that first since I do that in another workflow. Appreciate your help!

Labels