Converting Strings to Date/Time Format in Alteryx

“Learn to walk before you start running”
-Carl Allchin

Today was day 2 for DS11 and we took our first steps with Alteryx. After leading us by the hand through our first workflow, Carl then encouraged us to try and limp our way through some pretty convoluted data sets. Andrew and I had the pleasure of working on a set of seafood recipes (Yum.) with the aim of preparing the data for visualisation.

The Data

You want to keep track of time when you’re cooking, and our data set included thee columns containing temporal values: Prep time, cooking time and the total time required, which included any added time for resting, cooling etc. Our ultimate goal was to create a stacked bar chart that would show at a single glance how much time each recipe requires, as you can see in Andrew’s post.

What we initially found looked as follows:

The issue: Our values were not in date/time format, so we would need to convert them. Enter Alteryx.

The Alteryx Workflow

Our main tool for the task would be the DateTime function which converts strings and other formats to date/time format.

We started with the column Prep Time. First, we needed to add a filter to separate rows with only minutes from those which both hours and minutes. Selecting the column “Prep Time”, we filtered out rows containing the word “hour”.

Then we added a DateTime function for both groups to convert the strings into date/time format. The custom format for the incoming string field was “hh hours mm min” for rows with hours and minutes, and “mm min” for  rows with only minutes. Alteryx has a neat Example function which gives a preview for what the result is going to look like.

The results would come under a new column called Preparation Time. Lastly, we used Union to merge the two separate sets back together. Our workflow ended up looking like this:

We then repeated this process for the other two columns, “Cook Time” and “Ready in”. We ended up with something that looked a lot more useful:

Little did we know that we were still a far cry from having something we could work with in Tableau – but if you’re not supposed to run before you’ve learnt to walk, you probably shouldn’t have a go at triple jump, either.