Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

Data Prep Time format

Highlighted
8 - Asteroid

Hi Alteryx Community,

 

Its been a while, happy 2018!

 

I have a time formatting issue that I hope you can help with?

 

I have a data set with a time format that varies randomly through the database set as follows:

 

Time

10:02:03

11:05:01

742

1215

23:30

21:05

22:52:01

2252

  09:01:02 ( 2 spaces in front)

  10:02:22

10:01:55

 

I would like to transform to consistent format of hh:mm:ss for entire column. 

Thanks

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@BarleyCorn,

 

Quick Solve with 1 formula tool & a few formulas.  You can nest it into 1 formula or use 1 formula with IF THEN ELSEIF... But the underlying logic is:

 

Time_out (String) is:

 

 

Trim([Time])

...

 

iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out],
REGEX_Replace([Time], "(\d{2}):(\d{2})", "$1:$2:00"))

...

iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out],
REGEX_Replace([Time], "(\d{2})(\d{2})", "$1:$2:00"))

...

iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out],
REGEX_Replace([Time], "(\d{1})(\d{2})", "0$1:$2:00"))

...

 
iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out],
REGEX_Replace([Time], "(\d):(\d+)", "0$1:$2:00"))

Now use a select tool to convert to TIME data type.

 

Cheers,

Mark

 

 

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
13 - Pulsar

Another option:

 

image.png

 

Note that in the Data Cleansing tool I've selected to remove "Leading and Trailing Whitespace" and "Punctuation".

Highlighted
Alteryx Partner

hey @BarleyCorn,

 

Here is my suggestion option for your problem.
First, you need Data Cleansing Tool to remove ':' and leading space.

Then you need Formula Tool to check:-

if length=3 then '0'+[Time]+"00"

if length=4 then [Time]+"00"

if length=6 then [Time]

 

Final result you will get in format HH:MM:SS. Take a look at my workflow to get better understanding.

Highlighted
8 - Asteroid

Hi 

 

I posted this question yesterday - but got no replies and seems now not to be on Community??

 

I have a time format issue.

 

I would like to format a time variable that has an inconsistent format in the input excel as follows:

 

10:20:01

12:52:01

754

1252

13:01:02

  10:05 (two spaces in front)

  11:30

12:55:01

923

 

I would like to transform into column in HH:MM:SS format.

 

Many thanks

 

 

 

Highlighted
8 - Asteroid

Sorry all - Replies have gone into my Spam!

 

I'll look at now.

 

Cheers

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
You should have noticed that after a few minutes of no solution posts! We’ve got your back.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Tom,

 

Welcome to the community might be the issue.  Posts like this should go to Data Preparation & Blending.  A moderator has likely moved the post on your behalf.

 

https://community.alteryx.com/t5/Data-Preparation-Blending/Data-Prep-Time-format/m-p/117538#M20128

 

You've got many replies.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx
Alteryx

Hi All,

 

Just a quick note - I've merged the two threads on this topic.

 

Thanks!

Jess Silveri
Premium Support Advisor | Alteryx
Highlighted
8 - Asteroid

Thanks Mark and danrh but for me Syarifhidayat has a pretty good simple solution.

 

As always really appreciate your help!  

Labels