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

Use REGEX to Split a coloumn with no space

itahir04
8 - Asteroid

Hi team,

I have a coloumn with below values , current Coloumn type is V-String

PT0H0M17S
PT0H0M7S

PT00H39M06S

 

These values are showing time. Like 0 hour, 0M, 17 seconds (First value.)


My Objectove is to convert this cell value to a new coloumn that shows timestamp.

Like PT

Duration                               Timestamp

PT0H0M17S00:00:17
PT00H39M06S00:39:06
PT00H00M20S00:00:20

 

I was thinking to remove first two ch, split coloumn after H, M to get hours and minutes and seconds. Then remove last CH S using REGEX but I don't know how to use it.

Then bring everything together to create time stamp.

Can you please assist me,

Thanks,

 

 

 

6 REPLIES 6
AniK
7 - Meteor

You can follow the below steps to achieve the result. I have attached the completed workflow. 

1) Use Text to Columns to split the data

2) Use RegEx replace function in formula to strip off text. So only numbers remain

3) Use Date time to get the required output

pic1.JPG

Loic
Alteryx
Alteryx

Hi @itahir04 

 

In Designer: open the example for the tool RegEX:

Regex-OpenExample.png

 

Then go there: https://help.alteryx.com/current/boost/syntax_perl.html you will need to understand that type of syntax for this tool.

 

In your case the pattern of your string is: (PT)(.*)(H)(.*)(M)(.*)(S)

Each group under () is a field .* means any number - it will create 7 fields.

Field 2 contains hour, Field 4 contains Minutes, Field 6 contains seconds

Concatenate using Formula Tool and convert to time stamp using DateTime tool

 

RegEx-Example.PNG

 

 

 

 

 

ponraj
13 - Pulsar

Sample workflow as below. 

 

regex.PNG

itahir04
8 - Asteroid

Thanks Poonraj for providing a very simple solution.

 

Regards,

Rana

itahir04
8 - Asteroid

Thanks Aloc, great simple logic. THis provides me a perfect solution.

Thanks,

Rana

itahir04
8 - Asteroid

Thanks Anik,

Great way of solving this issue. Great solution.

 

Thanks for support,

Rana

Labels