Date parsing can be a tricky business in Alteryx and Tableau. To make it easier for myself and others to understand, I decided to write a blog on the topic.
Data Parsing is a relatively new topic to the DSAU5 cohort, and we all seem to love it so far. The tricky part about data parsing is knowing the rules that surround data formats in Alteryx, and to some extent Tableau. Not knowing about data parsing could be the difference between an event occurring on the 11th of September of the 9th of November as date formats and indeed calendars can be different across countries.
Parsing in Alteryx
Below can be seen the table for what could be outputted depending on the format string in Alteryx selected
| Specifier | Result |
| %a | The abbreviated weekday name (Mon, Fri) |
| %A | The long weekday name (Monday, Friday) |
| %b | The abbreviated month name (Feb, Oct) |
| %B | The full month name (February, October) |
| %c | The date and time for the computer’s locale. |
| %d | Day of the month as two digits, from 01 to 31 |
| %D | Equivalent to %m/%d/%y. |
| %H | Hours in a twenty-four-hour clock, from 00 to 23. |
| %I | The hour on a twelve-hour clock, from 00 to 12. |
| %j | The day of the year from 001 to 365 (or 366 in leap years). |
| %m | The month as a two-digit number (01 to 12). |
| %M | Minutes as two digits, from 00 to 59. |
| %p | AM or PM (capital letters). |
| %S | Seconds, two digits from 01 to 59. |
| %T | Time in twenty-four-hour notation. Equivalent to %H:%M:%S. |
| %U | This will return the week number, as 00 to 53, with the beginning of weeks as Sunday. |
| %W | This will return the week number, as 00 to 53, with the beginning of weeks as Monday. |
| %X | Adds “AM” or “PM” to the time format. |
| %x | The date, specified by the current locale. |
| %y | The two-digit year. |
| %Y | The four-digit year. |
| %z or %Z | This will give you the time zone, if applicable. |
.A few examples are listed below as to how you can structure different formats in Alteryx for the date 22nd of April 2008. Notice that the spaces, dashes, and forward slashes are important in the format of the string. If a format is incorrectly entered then the string will not parse to a data.
| Format String in Alteryx | Result |
| %d-%b-%y | 22-Apr-08 |
| %A,%d %B,%Y | Tuesday, 22 April, 2008 |
| %d-%m-%y | 22-04-08 |
| %d-%m-%Y | 22-04-2008 |
| %d %B, %Y | 22 April, 2008 |
| %d/%m/%y | 22/04/08 |
| %d/%m/%Y | 22/04/2008 |
| %a, %B %d, %Y | Tue, April 22, 2008 |
| %m-%d-%y | 04-22-08 |
| %m-%d-%Y | 04-22-2008 |
| %m/%d/%y | 04/22/08 |
| %m/%d/%Y | 04/22/2008 |
| %b %d | Apr 22 |
| %B %d, %Y | April 22, 2008 |
| %B, %Y | April, 2008 |
| %Y-%m-%d | 2008-04-22 |
| %Y%m%d | 20080422 |
Parsing in Tableau
Now if we wanted to do something similar in Tableau with regards to data parsing there is a slightly different set of rules. They can be seen Below
| Date Part | Symbol | Example String | Example Format |
| Year | Y | 2016, 97, 2 | YYYY, YYY, YY, Y |
| Era | G | AD, Anno Domini | GGGG |
| Month | M | 9, 09, Sep, September | M, MM, MMM, MMMM |
| Week of year (1-52) | w | 8,27 | w, ww |
| Day of Month | d | 1, 15 | d, dd |
| Day of Year (1-365) | D | 23, 143 | DDD, DDD |
| Period | a | AM, am, PM | aa, aaaa |
| Hour (1-12), Hour (0-24) | h, H | 1, 16, 03 | h, HH, hh |
| Minute | m | 8,59 | m, mm |
| Second, Millisecond | s, A | 24, 2, 34532 | ss, s, AAAAA |
EXAMPLES
Tableau
Here there are no % signs to help out. If you needed to convert the STRING: February 1 2019 into a date format that Tableau can read then below would be the correct formula.
DATE(DATEPARSE(“MMMM d YYYY”,STRING]))
If there were dashes instead of spaces then they would need to be entered as well, just like as in Alteryx.
Once the data is in a good date format we can work out things like the difference between two sets of dates. This could be worked out for the difference in shipping date and order date for example by doing DATEDIFF(‘day’, [Order Date], [Ship Date])
Alteryx
To give an of an Alteryx workflow where this would be useful, if we had to pieces of data, one a start time, and one an end
Start: 2019-01-09 13:30:00 End: 2019-01-09 17:00:00
Where we wanted to found out the hourly difference between the two times. A datetime parse can be used with the custom expression : day %d/%m/%Y %I:%M %p
To break this down:
day means the day of the week
d% means the first two digits for day
/ is the separator
%m means the two digits for the month
%y means the two digits for the year
%I means the hour on a 24 hour clock
%M mean the minutes
%p specifies whether the string has AM or PM after
Once this is all done, we can use a formula like DateTimeDiff([End Date Time],[Start Date Time],’minutes’) / 60 to get the amount time between the two pieces of data in hours, as we convert to minutes and then divide by 60.
I may add to this blog if SQL can do similar things. Thank you for taking the time to read
Sources for the information here can be found on:
https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm
