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!

---

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.

 

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.

 

 

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.

 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.

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.

 

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.

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.