## 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).

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.

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).

STEP 2

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).

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!

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!

Simona Loffredo

Thu 17 Mar 2016

Fri 04 Mar 2016