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
SOLVED

Use REGEX to Split a coloumn with no space

Meteor

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,

 

 

 

Alteryx Partner

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

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

 

 

 

 

 

Quasar

Sample workflow as below. 

 

regex.PNG

Meteor

Thanks Poonraj for providing a very simple solution.

 

Regards,

Rana

Meteor

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

Thanks,

Rana

Meteor

Thanks Anik,

Great way of solving this issue. Great solution.

 

Thanks for support,

Rana

Labels