12 Replies Latest reply on May 30, 2012 4:53 AM by Jonathan Drummey

    String to Date Transform

      My input data for date is in this form: 'DDMMMYYYY' for example '10JAN2012'.  I have tried the following calculated variable definition to transform to recognized date field:

       

      DATE(
      LEFT([date_var], 2) +
      "-" +
      MID([date_var], 3, 3) +
      "-" +
      RIGHT([date_var], 2)
      )

       

      I get null value. 

       

      I'm thinking it might have to do with my MMM piece being all caps.  If so, is there a function to transform case of string data? (go from JAN to Jan?)

       

      Thanks,

       

      Kirk

        • 1. Re: String to Date Transform
          Jonathan Drummey

          Hi Kirk,

           

          I could not duplicate your problem using a text data source or a calculated field as the source of the string, I always got the correct results. What data source are you using?

           

          Jonathan

          • 2. Re: String to Date Transform

            The original source is a tab delimited text file.  I extracted the entire file into a .tde file for the actual analysis.

             

            When you attempted to duplicate the problem, did your source string data have the MMM part as JAN or Jan or jan?  A little experiement I ran suggested it made a difference.

             

            Thanks,

             

            Kirk

            • 3. Re: String to Date Transform
              Jonathan Drummey

              I learned something new today. Tableau's DATE() function works differently depending on whether the source data is text or an extract. I created a text file with a variety of formats then imported it into Tableau as a text file, and also as an extract. Here are the records:

               

              ID Date

              1 10JAN12

              2 11Jan12

              3 12jan12

              4 13JAN2012

              5 14Jan2012

              6 15jan2012

               

              You can see the results of the calculations in the "start here" dashboard. The original calculation you created works fine for any of these formats for the text data source, but returns Nulls or even a different interpetation of the data for the extract. With that information in hand, I created a "Working Calculation" field with the following formula that works for both DDMMMYYYY and DDMmmYYYY formats:

               

              DATE(LEFT([DateString],2) + "-" + MID([DateString],3,1) + LOWER(MID([DateString],4,2)) + "-" + RIGHT([DateString],4))

               

              I'm submitting this to Tableau Tech Support, at the very least this behavior should be documented somewhere, if not made the same across data sources.

               

              Jonathan

              • 4. Re: String to Date Transform

                There appears to be something in play with the Data Connection choice.  When I choose Connect live to my txt file, the string field imports as expected, and I can run the calculated field definition stated above successfully.  However, when I choose Import all data, the same calculated field defintion returns null values.

                • 5. Re: String to Date Transform

                  My post above was prior to me seeing your last post.  I'm going to check out your dashboard file now.  Thanks for working with me on this.

                  • 6. Re: String to Date Transform
                    Jonathan Drummey

                    You're welcome!

                    • 7. Re: String to Date Transform

                      This new calculation definition works with the string data I extracted.  Thanks!

                      • 8. Re: String to Date Transform
                        Jonathan Drummey

                        You're welcome, Kirk!

                         

                        Also, I found out from Tableau tech support that (at least for the current version 7.0.4) it is expected behavior that the DATE() function works differently for text files vs. extracts.

                         

                        Jonathan

                        • 9. Re: String to Date Transform
                          Ted Corbett

                          Hi Jonathan,

                           

                          I have another related question perhaps you have advice based on this string of work.  I have two fields Admit Date and Admit Time.  When I combine them using a calculated field to create a DateTime field I can see the discrete data when using a live connection, but the calculation performs differently on an Extract.  On an extract it only shows the time at midnight. 

                           

                          I've attached a sample workbook highlighting the difference. 

                           

                          Any thoughts?

                           

                          Ted

                          • 10. Re: String to Date Transform
                            Jonathan Drummey

                            Hi Ted,

                             

                            I have any advice when I saw your question, but now I do. It turns out that given a datetime and an Excel data source the STR() function returns just the date for fields with a time of 12:00:00 AM and just the date for fields with a date of 12/30/1899. Whereas for an extract, given a datetime the STR() function returns the entire datetime. I suspect this is due to Tableau using the JET engine for Excel/Access/text and its own code for the extract, but I could be wrong.

                             

                             

                            In your workbook, I started by taking the DATETIME off the Admit Date Time Extract field to create the Admit Date Time Extract String (original) field. We can see that the field is returning the original time for the Admit Date along with the time for the Admit Time field, so we can now understand why the function was failing to produce expected results.

                             

                            Therefore, what I did was wrapped the Admit Date inside the DATE() function, so the STR() function now looks like STR(DATE([Admit Date])) and that gets us a working string, seen in the Admit Date Time Extract String (updated) field and the same formula works to get a DATETIME value, as seen in the Admit Date Time Extract (updated) field. The good news is that the updated function will work for both types of data sources.

                            Jonathan

                            • 11. Re: String to Date Transform
                              Ted Corbett

                              Fantastic.  Thanks for your very quick reply, that does indeed do what I wanted.  I knew it had to be something simple, but I never tried that. 

                              • 12. Re: String to Date Transform
                                Jonathan Drummey

                                You're welcome! I asked Tableau tech support to document the issue with DATE() behaving differently depending on the data source, I'll also ask them with regards to the STR() function so hopefully it won't come up again.

                                 

                                Jonathan