We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
MarqueeCrew
20 - Arcturus
20 - Arcturus

I have a dataset that has a bunch of date fields in the following format "DD-MMM-YYYY".  It seems like I have to go thru a convoluted process to convert from a string to date. I dont understand why I can't change the type simply using the select configuration.  Is anyone else annoyed with how alteryx handles dates? Seems like it should be easier. 


Thanks,

@fitzviz

For those who need to see the answer to the question first, you can use a formula like this or change [Field1] to [_CurrentField_] and use a Multi-Field Formula tool:

 

DateTimeParse([Field1],"%d-%b-%y")

How did I know the answer to this question?  Was it because I am an ACE?  Do I have a photographic memory?  None of these are right.  I knew the answer because I knew where to lookup the date formats (DateTime Format Help).  Based upon this question and the presence of frustration, I have opted to write this blog and (as a present for those who read it) I have included an incubator macro (something that I'm playing with) that isn't generally available.  My solution post (as of now) hasn't been starred or accepted as a solution, so I don't know if it helped Mike, but let's assume that it has.  The community has literally 1,000 "Date" posts in the last year.  While I could make a bundle of SOLVES if this continues, it does seem like there are an awful lot of questions regarding dates (note: not all are transformation questions).

 

Alternative approaches to working with dates include the BB Date tool and the DateTime tool.  I've even created an idea (Alteryx making dating easier) that you can STAR to change the way that the product handles dates.  Until dating becomes easier, here is a new approach to handling dates.

 

Read the manual: Format strings

Date/Time format strings are used by the DateTimeParse and DateTimeFormat functions to tell Alteryx how to parse (read) or format (write) date/time values. Format strings are comprised of specifiers and separators.

 

Please note that I didn't include the full help article here.  I was even going to show fewer rows (e.g. remove time rows) but wanted everyone to get a good taste for the possibilities.  The help article shows both how to convert strings to dates with DateTimeParse() as well as how to format dates back into strings.  For simplicity, I am looking to help with the conversion to date fields here.  So let's focus on the parse function.

 

The syntax is DateTimeParse([Fieldname],<Format>).  This means that the format can either be a variable in the incoming data or it can be a string.  If it is a string, then the formatted function would look like this for a sample start date field with a value of 23-Jul-1990:

 

DateTimeParse([Start Date], "%d-%b-%Y")

%d was chosen as the day of the month then '-' was the delimiter used followed by %b as the abbreviated month name followed by '-' followed by %Y as the 4-digit year.  As long as all three components of the date are present in the data, you should be able to construct a formula that meets your specific needs.

 

capture.png

 

For fun I created a macro that would sample the first 100,000 records from an input file and would find the most prevalent description of the incoming date formats (for non-null values).  It can discern between 04/01/2018 as April 1st and January 4th (localization) if dates exist that make this clear (e.g. 04/13/2018) in that date field.  If multiple formats exist for incoming dates, the Pct (percentage) column falls below 100%.  Instead of showing each format, my thought was that you'll need to do some data investigation to better understand the source of the data.  This "tool" macro can help you to understand the Parse function as a beginner and prepare you to better help others in the future.

 

What formats does it help with?  Many formats are supported (the delimiter is any non-word character):

  • 10-23-2018
  • 10-23-18
  • 23-10-2018
  • 23-10-18
  • Oct-23-2018
  • Oct-23-18
  • 23-Oct-2018
  • 23-Oct-18
  • 2018-23-10
  • 18-23-10
  • 2018-Oct-23
  • 18-Oct-23

I didn't try to make all formats and I honestly didn't test all of these formats either.  What I did do was create a "roughly right" macro and would ask you to test it out for me.  See if it helps you and suggest ways to improve it.  Take a look at the macro (open it) and see how I got the results.  Here is a little logic for you:

 

REGEX_Match([Field1],"\l{3}\W+([1-9]|0[1-9]|1\d|2\d|3[01])\W+(19\d{2}|2\d{3})")

If this statement is true, then the format is:  "%b-%d-%Y" where the - is whichever delimiter is really in your data.

 

capture.png

 

I calculate a series of formats (e.g. b-d-YY) as boolean variables (True or False) and then convert the True values to expressions.  I do a little magic and then pop out my guess for the format.  Granted, I could actually transform the data and could allow you to do this for more than one field at a time.  But alas, I'm not so inclined.  I do want to help the Alteryx community.  I welcome your feedback here and hope that if you are reading this post that it not only helps you to solve your current challenge but also gives you a forum to voice your opinions.  Please feel free to download and to try the tool out.  If there are problems here or if you think of ways to improve this tool, then I might publish a version as part of the CReW macros.

 

Cheers,

Mark

Comments