After finishing my week 1 of Alteryx Challenge (see post), I thought it would be nice to have some sort of structure to it and so I decided to do the challenges by topic. This allows me to work on similar problems together and I plan to continue with a series of blog posts that explain the tools and approaches I used. The first post in the series on basic tools you can find here.
Because dates are a common data type to deal with and to some point intuitive, I chose to start with them. As it turned out, there were only 8 challenges with ‘date’ in the name and I have already completed a few of them. Here’s my summary of dealing with dates in Alteryx.
Know your format
Even though some programs allow you to change the way a date looks like, Alteryx will always have it in the same format: ‘yyyy-mm-dd’. This means that when you’re importing data from Excel, as long as the cell type was date, it will show up in Alteryx in that ‘yyyy-mm-dd’ format. Don’t panic, it’s all good.
If you read a .csv file, all fields will be interpreted as strings and so your date may look like in the original file but it won’t be treated as date. There are a few functions that allow you to turn a string into a date and some even have the to option of specifying the format of the string to make the type conversion just how you like it!
String to Date
I haven’t used much toDate(x) function, so I’ll focus on DateTimeParse() which I’ve found to be very useful.
DateTimeParse(string, format) takes two arguments: string (dt in original documentation) which contains the data within it and format (f in original documentation, also in a form of a string) specifies how the data should be extracted from the string argument. To create the format argument, you will need to know your specifiers. Specifiers are short expressions that encode date parts. If this sounds like magic to you, relax, there’s a list of specifiers in Alteryx documentation:
So. Freaking. Useful.
Below the Specifiers you’ll find separators but that list is definitely not exhaustive so I wouldn’t bother paying much attention to it.
Another way to convert one data type into a date is to use DateTime Tool in which you can either choose from a pre-specified list of date formats or create your own template.
Or in function terms, DateTimeDiff(). It may prove to be a very sensible function once you remember which date is subtracted from which. For unknown to me reason, the documentation lists the arguments as dt1, dt2, u which corresponds to date1, date2 and unit. What I like to use instead is end date, start date and interval (although unit is not so bad in this case).
You may have guessed that DateTimeDiff() returns the difference between the given dates in the unit (interval) of choice. The important thing to remember is that the returned difference is a whole number (integer). If you want to get a specific level of granularity, you’ll have to use the unit of the lowest level and then figure out the other values. This is actually one of the weekly challenges.
It seems that to get a boolean comparison of dates you could also use <, >, =, etc. operators. Well, I’ve seen them in some expressions and they seemed to work.
A side note: challenges #46 and #58 seem identical to me but you can still try to do them in a different way (that’s what I did).
That’s it from me on dates in Alteryx. If I find out more interesting bits, I’ll make sure to write a blog on them as well. Thank you and good night!