How to transform Duration (hh:mm:ss) in seconds with Alteryx

by Simona Loffredo

This week was really challenging because Andy decided that every day we had to do a different viz.

Also data preparation has been challenging. Yesterday in the late afternoon, after struggling for some time on a couple of things I needed to solve in Alteryx, I took some time off to clear my mind: swimming and a great sushi for dinner have helped me! 🙂

 

If you will ever need to transform a duration into seconds, this might help.

I have used the duration into this viz (then,I transformed it into minutes creating a calculated field in Tableau).

Duration in tableau

In this dataset I had the duration of Andy’s training activities (running, ciclying, walking) for each single raw shown like hh:mm:ss (ex. 2:34:6). I really needed to have duration in seconds in order to use it in Tableau (otherwise Tableau does not recognise it as a number). This is a piece of my Alteryx workflow focused on this issue.

Piece of WF Alteryx

STEP 1

In order to calculate the duration in seconds, I need to consider that there are 60 seconds in each minute and 60 minutes in each hour -> I need to do this calculation: (hh*3600 + mm*60 + ss). But before, I need to split the columns and… Text to Column is our guy (below the configuration of the tool).

text to column

 

STEP 2

Always check your data!

After splitting the columns into three columns, I noticed that the data were not homogeneous in terms of duration. Some data did not have hours but mm:ss. This means that in column “Duration 1” I had both hours and minutes, in column “Duration 2” I had both minutes and seconds and in column “Duration 3” I had seconds or null (see picture below).

duration problem

 

STEP 3

This calculation (hh*3600 + mm*60 + ss) is still correct but I need to put it into the context in order to let it work properly and calculate the correct values. Creating the following formula the problem is solved!

Formula duration

Basically, checking if the column “Duration 3” is null, I am telling to Alteryx to use the formula for mm:ss (mm*60 + ss) and to use the complete formula in the other case.

I hope this can be useful!