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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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