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

Data Prep Time format

BarleyCorn
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

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

Another option:

 

image.png

 

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

Syarifhidayat
8 - Asteroid

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.

BarleyCorn
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

 

 

 

BarleyCorn
8 - Asteroid

Sorry all - Replies have gone into my Spam!

 

I'll look at now.

 

Cheers

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
JessicaS
Alteryx Alumni (Retired)

Hi All,

 

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

 

Thanks!

Jess Silveri
Manager, Technical Account Management | Alteryx
BarleyCorn
8 - Asteroid

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

 

As always really appreciate your help!  

Labels