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

Add a character after every nth charachter in a string.

dgsigl2
6 - Meteoroid

Hello Community,

 

Looking for some help to allow me to finish off a project that I am working on.  The concept is fairly straight forward but I am having issues resolving.  Basically, I have records where I want to add a delimiter after every 4th value so I can use text to columns to split our the 4 character ID fields I need to isolate.  I feel like the solution is a regex expression of formula of some kind.

 

Once I can successfully split out the values using text to columns, is there a way to pivot the data from each row into one column with all the values?

 

Any help would be greatly appreciated!

 

Thank you,

Dan

7 REPLIES 7
PhilipMannering
16 - Nebula
16 - Nebula

Hi dgsigl2

 

Assuming I've understood you correctly (which is a massive assumption), this might help you (see below)

Using the regex tool in tokenize mode it splits every 4 character into a separate row. The regex expression is simply four periods/full stops: ....

Use recordID beforehand if you want to keep track of which row they came from.

 

Capture.PNG

MarqueeCrew
20 - Arcturus
20 - Arcturus

Hello @dgsigl2,

 

No picture here.  I have a working solution, but I'm going to describe it to you.

 

  • SELECT
    • You need to make sure that the input data length is long enough to support the addition of dots into the values.
  • RegEx
    • Field to Parse:  this is your input field
    • Regular Expression:  
      (.{1,4})
      A group of characters with a length of 1 to 4.  This will cycle through the 4 digit values.  If you end up with a last set of characters that is less than 4 it will put them into that final group.  If you only allow for 4 as the length, it would DROP the last values.
  • Formula
    • Remove the final . from the end of the field
      Trim([Input], ".")

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
LordNeilLord
15 - Aurora
Ignore me
MarqueeCrew
20 - Arcturus
20 - Arcturus

@dgsigl2,

 

While I like the .... aspect of @PhilipMannering's solution and appreciate @LordNeilLord providing you with a more complete solution, I do want to point out that the solution provided REQUIRES that the sets all be 4 characters in length.  If the input is ABCDEFG, the result will drop the last characters because there are not 4 characters in that set/group.

 

I prefer my solution because it does not require a shift to ROWS and then a shift back to a single field.  If you want to use that solution, please modify the .... to  .{1,4}

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ponraj
13 - Pulsar

Here is the sample workflow for your case... Regex \w{4} with output method tokenize with properties of split to rows, recording id, summarize and join tools will get you the desired output. 

 

Workflow.PNGResults.PNG

PhilipMannering
16 - Nebula
16 - Nebula

@MarqueeCrew Good point on using the expression .{1,4} if you're codes aren't a length that is a multiple of 4. Hadn't thought of that.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@PhilipMannering,

 

I hadn't thought of it either, until I tested a similar post and saw it in the sample data.  Now having FAILED ( @mceleavey knows that I am human) with that posts data I learned my lesson.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels