What I absolutely love in Alteryx is the flexibility it allows its users to approach any problem. Subject to your Alteryx or coding experience, your thinking process or sometimes due to shear luck, you might come up with the most simple and robust solutions. However, there are those times when you might lack the mind clarity and you tend to overcomplicate things for no particular reason. Those into the Alteryx weekly challenges world know exactly what I’m talking about.
This blog will be about a very niche new trick I learnt whilst trying to solve Alteryx Weekly Challenge #106. This challenge is about analysing the times that inhabitants enter and exit rooms within their household. The provided dataset is rather simple, with three string fields one defining the household’s different rooms and the other two the entry and exit times from each room. Note that the times given are in an AM/PM format.
Our first step is to bring the times given in AM/PM format into something that Alteryx understands as a time field.
The not so proper way to do it
Well, my thinking process at this stage was pretty straightforward:
- Differentiate whether the times in each of of the “Enter” – “Exit” fields refer to AM/PM times. To do that, I thought I should parse on the whitespace between the time (xx:xx or x:xx) and the two characters at the end of each string. The Text To Columns tool would serve me well in this instance.
- Then, based on the newly created AM/PM columns, I should come up with a crazy formula tool to convert the times to a format that Alteryx would understand as times.
The output at my Formula Tool looked something like that:
The two Text to Columns fields parsed the time from the AM/PM and based on that a not so simple expression in the formula tool helped me bring the times in the correct format. THIS EXPRESSION!
At this point I was quite happy with myself that I actually made this formula tool work properly. However, deep inside I knew that there should be an easier way to do that. So I went online to find an easier answer.
The proper way to do it
I knew that the DateTime Parse tool doesn’t not have the option to parse time in the AM/PM format. I was also aware that the formula tool has a bunch of embedded DateTime functions, so if the answer was somewhere to be found, it should be there. Alteryx has a very helpful guide that I have used in the past for all it’s Date Time Functions . If you use the Formula tool to format date time fields, this is something like the Bible.
Hidden between all those rows, percentage signs and functions, I found what I was looking for. Behold a rather simpler expression.
Yes, yes that single line manages to do exactly the same thing as the formula I’ve come up with. I always enjoy learning new things and from now on I’ll never forget how to parse time fields in an AM/PM format. After all, I know two different ways to do that.
Thank you for reading my blog. Hopefully that tip will save you some time if you are facing a similar task.
