Sure, that should work.
I would have created a separate calc in the primary source to convert it:
And then used that to blend with the excel field, but that probably isn't necessary. It's just my style.
The excel date has a full date in it, right? (It's not just OCT-2015, NOV-2015, ..., for example.) Full dates should match up with full dates.
I'm just wondering if there isn't something else going on with the data. And without a sample workbook, all anyone can do is offer guesses.
Okay I attached the packaged workbook to the original message. Let me know if that works.
1 of 1 people found this helpful
Well I can't figure out how to make Tableau see the SQL Date field as a DATE() and not a DATETIME() for the purposes of the join.
I did two things that you can consider.
In the first attachment (...Extract 2), I broke the join. I took your XLS and added a measure field (just to show that the blend is working), and added the XLS as a totally new data source. then I blended on date, and displayed sum(value) in Sheet 1 to show it's working.
In the second attachment (... Extract 3) I changed the field data types so that both are date-time. (Click on the little date icon above each field. In the attached the icon is now a date-time icon because that's what I changed it to.) Once both are date-time, the error goes away. But only on the datasource page. If you try to use this join, nothing works, and maybe that's due to the way that SQL vs Excel fills the time portion of the field. I don't know. And I also cannot explain why the SQL date field insists on presenting itself to Tableau as a date-time when you try to make this join. Even when you re-click the icons and change it back from date-time to date, the mismatch comes back.
If blending won't do the job for you, maybe you need to file a support ticket to find out what's happening under the hood here.
1 of 1 people found this helpful
Given that your original SQL source is a custom SQL query, have you considered altering your SQL to make the conversion in there?
I.e. SELECT CONVERT(date, my_date_field)
Thanks for your help, Joe. I really need the join to get the result I need, so I will send this to Tableau support. The fact that you can't figure out why Tableau is doing this validates that I'm not crazy .
Have you tried what Mark suggested? I like that idea. Eliminate that first.
Hi Tom, I didn't know about CONVERT. Before I try that, could you tell me if CONVERT will only temporarily change the type of data returned in the query, or will it change the data in the table on the SQL database? I really do not want to mess with any of the underlying data in the table itself.
A SELECT statement cannot alter underlying data in your database, it's impossible.
Applying the CONVERT within a SELECT statement will only affect the result set you are generating and bringing into Tableau.
Great! That is just what I want.
I tried it out and it works. Thank you! I still sent Tableau a support ticket, as this should be working without having to do this, but your suggestion gets me by.
We are still considering whether to allow user to join on "similar" data types however that's something that can go wrong really fast. Some databases do allow it and handle conversion in their own special way so in theory we can do something similar.
One thing to note - with Text and Excel files you can change data type of the column directly with type switcher (like the one highlighted on your screenshot) - it'll force Tableau to raparse the file and get the data type you've set so in your case you could convert Excel's column into "Date & Time" instead - a bit simpler than writing Custom SQL.
Note that this ONLY works for Text, Excel and Google Sheet connectors. Like you already mentioned - changing type on SQL won't affect join conditions as type selected for rest of connectors only affect output fields of the query.
This feature would be greatly appreciated!
Our company recently updated most of the database tables so that anything that had a "date" datatype how has a "datetime" datatype with 12:00:00 AM as the time portion. This breaks a lot of the joins that we have because in some tables they are date fields and in others they are datetime fields. To work around this, we had to add an additional field to all of the tables that are still date fields so that we have both date and datetime fields for the same data.
Not only was this a hassle, but it is using more storage for the same data just to make it work in Tableau.
I too got the same error, but when i try do it in SQL level(Oracle Developer) the above command(Convert) is not working. It throwing an error convert function can't be used in the character set.
Anyone has solution to join the data instead of blending.Has anyone had raised the concern to tableau support and got it resolved?
Please share your thoughts.
If you can't do the join in Oracle SQL then it's a problem with your query, not with Tableau.
You'd need to share the exact error message an example of your SQL for more help. I'd suggest you google that error though and take a look at those results.