How to Work With Flat-File Dates in #Alteryx

Introduction

I previously wrote about solving seemingly impossible problems with Alteryx. While writing the workflows for the challenging problem I described in that article, I discovered a few interesting aspects of how Alteryx handles leading zero fields and performs date field conversions.

Since these topics took me a bit of testing to understand, I thought that these insights would be interesting to document and share. Of course there might be other techniques to handle the situations that I describe, but for me, the techniques I describe work reliably and there is not a lot of documentation on the web for these topics.To read about handling leading zero fields in Alteryx, click here.

The topic to be addressed in this blog post is very specific and is limited to handling dates that originate in flat-files. If you are building a workflow that ultimately will create a Tableau Data Extract (*.tde file), you will definitely want to read this post.


If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!

Enter your email address to follow this blog and receive notifications of new posts by email.


Creating Date Fields From Flat Files

I previously wrote about a great success I had in processing a fairly complex flat-file with Alteryx. If you need to process data that originates in a flat file and you haven’t done it in Alteryx before, you should consider reading that post before continuing with this article. I’m going to jump right to the solution without a lot of explanation because I want this article to be concise.

Alteryx does not allow you to directly read a date from a flat-file. You have to read date fields as strings. In the example I show below, I had 34 date fields in the flat-file that all were stored in the format of mmddyyyy. Each date is precisely 8 characters wide. Figure 1 shows the XML commands I used for reading these date fields, and as you can see I just read them as V-Strings of length 8.

Figure 1 - The flat-file XML commands used to read two date fields: Admdate and DisDate.

Figure 1 – The flat-file XML commands used to read two date fields: ADMDATE and DISDATE.

 

Approach #1 – My First Attempt At Date Conversion

Once you have these strings in memory, you will want to convert them to a date if you are going to operate on the dates. Initially, I thought that if I converted the date strings to a standard format of mm/dd/yyyy and told Alteryx to convert them to a date (data type), that this would be sufficient and accurate. Figure 2 shows how this is done in Alteryx using the Multi-Field formula tool combined with regular expression replacement. This replacement simply takes the string format of mmddyyyy and turns it into mm/dd/yyyy.

 

Figure 2 - Using the Multi-Field Formula tool for converting all selected date fields (initially strings) to dates (as Alteryx understands dates).

Figure 2 – Using the Multi-Field Formula tool for converting all selected date fields (initially strings) to dates (as Alteryx understands dates).

 

Option 1 – Change your mmddyyyy string to a date variable in the form of mm/dd/yyyy

If you check the box that says “Change Output Type to:” and set it to a date (a date data type, not a date format), this operation will not work. Alteryx will report a conversion error, which will cause you some consternation because you think a date like “11/12/2013” is perfectly formatted as a date and could be converted to a date data type. This approach fails for a particular reason that I explain in the next section (see Approach #2).

Option 2 – Change your mmddyyyy string to another string variable in the form mm/dd/yyyy

If you do not check the box, the field will be converted into a new string of  length 10 in the format of mm/dd/yyyy. You can write these fields to a csv file that Tableau can use. If you are only interested in writing the data to Tableau via a csv file, this approach is OK (don’t check the box). The string format of mm/dd/yyyy is fine and Tableau will be happy to read that from a csv file. If you continue with this set-up (your dates are still strings), recognize that if you choose to write a *.tde file directly from Alteryx, Tableau will view these fields as strings in the data extract, not as dates as you intended. If you want to write your date fields directly a *.tde file in Alteryx, you must continue to read this post to find the solution in the next section.

 Approach #2a – My Second (and Correct) Attempt At Date Conversion

The reason that Option #1 above fails is that the date format of mm/dd/yyyy is not what is supported by Alteryx for date data types.  If you are going to create dates within Alteryx, you need to use the international date format of yyyy-mm-dd.

Figure 3 shows the proper configuration for creating the date fields in Alteryx, when you are starting with a date that is in the form of mmddyyyy (with no forward slashes). By using this regular expression replacement, the dates will be properly formatted and the conversion of the string date field will be completed successfully. Your date fields will be treated as dates and when they are written to a *.tde file, Tableau will recognize them as valid date fields.  Hopefully this explanation will save you some time when working with date fields that originate in flat-files.

Figure 3 - The proper settings needed to convert a flat-file string to a date field in Alteryx.

Figure 3 – The proper settings needed to convert a flat-file string in the format of mmddyyyy to a date field in Alteryx.

 Approach #2b – My Second (and Correct) Attempt At Date Conversion

Figure 4 shows the proper configuration for creating the date fields in Alteryx, when you are starting with a date that is in the form of mm/dd/yyyy (with forward slashes, although this format is not likely to be present in flat-files). By using this regular expression replacement, the dates will be properly formatted and the conversion of the string date field will be completed successfully. Your date fields will be treated as dates and when they are written to a *.tde file, Tableau will recognize them as valid date fields.  Hopefully this explanation will save you some time when working with date fields that originate in flat-files.

Stand_dates

Figure 4 – The proper settings needed to convert a string in the format of mm/dd/yyyy to a date field in Alteryx.

Approach #3 – The Joe Mako DateTimeParse Conversion Method

Writing blog posts can be a funny thing. You send bits of information out into the anonymous world wide web and great things can come back to you without you knowing that they are coming. Such was the case with this post.  After publishing it, I got a quick response from a friend of mine named Joe Mako.  Click here if you want to learn more about him, and trust me, you will want to know more about him if you are interested in Tableau or Alteryx. Joe gave me another solution to this problem that had escaped me.

The funny thing is that Joe’s solution is nearly where I began this journey about two weeks ago. I had a couple of people recommend using the DateTime approach (including Alteryx Ace Joe Lipski), but when I read the Alteryx documentation for this routine, it indicated that the mmddyyyy format was currently not supported. So when Joe wrote me the comment, I didn’t see that the function he was suggesting I use was slightly different than the one I tried and is named the DateTimeParse function. Joe took the time to show me the documentation for this method (click here for this essential Alteryx reference that is very hard to find!) and how you can build custom fields such as the one he used here: ‘%m%d%Y’ as shown in Figure 5.

Figure 4 - An alternative method for converting the flat file dates to actual dates via Joe Mako.

Figure 5 – An alternative method for converting the flat file dates to actual dates via Joe Mako.

 

To reiterate what I said in the first paragraph, there are always more that one way to get things done in tools like Alteryx and Tableau. By building your experience base by solving a wide range of problems, you will learn efficient ways to get things done that make sense to you. Thanks to Joe for his comment and helping to improve this post!

Additional Thoughts on Dates

Once you have a date in Alteryx, you might want to do some summaries of the data.  Figure 6 shows one approach for summarizing dates on a monthly basis.  This is an extension of the concepts shown in Figure 5 above but leaves you with a string field to represent the date such as February 1, 2015.

Month_Summary

Figure 6 – A formula is used to extract the month from a date field (called New_EXPENSE_DATE). This field can be used as a “group by” field to aggregate across a month.

If you remain in Alteryx and want to create a valid date field for a monthly summary, you can use the settings shown in Figure 7.

Month_Summary_Ver2

Figure 7 – A formula is used to extract the month from a date field (called New_EXPENSE_DATE). Notice that the new field created is a date, compared to the string created in Figure 6. This field can be used as a “group by” field to aggregate across a month. I use this if I want to sort by date and then do something like running sums across a grouping.

 

7 thoughts on “How to Work With Flat-File Dates in #Alteryx

    • Hi Joe,

      That is an option for some users, but in my case that function was not applicable because the mmddyyyy format is not currently supported. The mmddyyyy format probably will be added in the future, so to do what I had to do, I had to develop this technique.

      Ken

  1. As a new comer to Alteryx, I just found your blog trying to understand the date formats in Alteryx. I’m currently devouring EVERYTHING you’ve got here as it’s a great resource. Thanks to Joe Mako as well!

  2. Pingback: Impressions From Our First Month Of Using #PowerBI | 3danim8's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s