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

Simple question - logic not working on String

Farhana91
6 - Meteoroid

Hi All,

 

Back with another question:

 

This needs to be dynamic:

 

jan2017Actuals

Feb2018PY

Mar2019Plan

 

 

I need these in three columns. The text to columns does not work since there is no delimiter.

Month  Year      Scenario

jan      2017      Actuals

Feb     2018      PY

Mar     2019      Plan

 

I tried the following: 

Month:left([Name],3)

Year : (RIGHT(LEFT([Name],7),4))

Scenario: (RIGHT(LEFT([Name],15),7))  DID NOT WORK!!!

 

 

Please Guide.

 

Regards,

Farhana

4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @Farhana91,

 

I would use the RegEx tool and configure it as follows:

  • Choose your field to parse
  • Regular Expression
    • (\w\w\w)(\d\d\d\d)(\w+)
      I've simplified the expression so that you are looking for 3 word characters followed by 4 numbers followed by 1 or more word characters
  • Output Method is Parse

Here's a picture of the workflow:

Capture.PNG

 

The workflow (v11) is attached.

 

Cheers,

Mark

 

P.S.  You can change the output field names in the parse tool, but since you'll also be adding a select to remove the original field I elected to make all of these changes in the select tool.

 

P.P.S.  How about a vote/star for our Analytic Excellence entry?  https://community.alteryx.com/t5/Alteryx-Analytics-Excellence/Excellence-Awards-2017-Adam-Rant-From-...

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ThizViz
11 - Bolide
You've got the right idea. And Alteryx, way to get text in the middle of a field is with the substring function. [Substring(String,start,length)]

So your second and third statements would be

Substring([Name],4,4)
and
Substring([Name],8,7)

In your case, your longest text on the right is 7 characters, but when I make this formula I often use 99 so it will pick up whatever is remaining in the field.

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
patrick_digan
17 - Castor
17 - Castor

@Farhana91 Nothing wrong with @MarqueeCrew's solution (I also love regex). I just wanted to point out that the substring function may be an option for you as well. For your scenario field, you would use something like:

Substring([Name],7)
Farhana91
6 - Meteoroid

Thank you all!! All the solutions perfectly worked :)

Labels