-
1. Re: String to Date Transform
Jonathan DrummeyMay 17, 2012 9:00 PM (in response to kirkrudolph0)
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
kirkrudolph0 May 18, 2012 8:09 AM (in response to Jonathan Drummey)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 DrummeyMay 18, 2012 9:06 AM (in response to kirkrudolph0)
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
-
string dates test.twbx.zip 34.2 KB
-
-
4. Re: String to Date Transform
kirkrudolph0 May 18, 2012 9:15 AM (in response to kirkrudolph0)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
kirkrudolph0 May 18, 2012 9:24 AM (in response to Jonathan Drummey)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 DrummeyMay 18, 2012 9:25 AM (in response to kirkrudolph0)
You're welcome!
-
7. Re: String to Date Transform
kirkrudolph0 May 18, 2012 9:41 AM (in response to Jonathan Drummey)This new calculation definition works with the string data I extracted. Thanks!
-
8. Re: String to Date Transform
Jonathan DrummeyMay 28, 2012 8:01 AM (in response to kirkrudolph0)
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 May 29, 2012 12:29 PM (in response to Jonathan Drummey)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
-
Time Example.twbx.zip 109.9 KB
-
-
10. Re: String to Date Transform
Jonathan DrummeyMay 29, 2012 1:10 PM (in response to Ted Corbett)
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
-
Time Example jtd edit.twbx.zip 173.4 KB
-
-
11. Re: String to Date Transform
Ted Corbett May 29, 2012 1:17 PM (in response to Jonathan Drummey)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 DrummeyMay 30, 2012 4:53 AM (in response to Ted Corbett)
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