1 of 1 people found this helpful
Are you trying to perform the join in SQL or in Tableau?
If you're doing it in SQL you could use either the FORMAT function or the DATEPART function.
An example using format:
SELECT .. FROM table1 t1 JOIN table2 t2 ON t1.Product = t2.Product AND t1.Machine = t2.Machine AND format(t1.DateField,'yyyy-MM') = format(t2.DateField,'yyyy-MM')
Thank you Tom.
Initially we want to perform the join in Tableau, is something like the query you wrote also possible in tableau? Or is that something you would advice against?
Are you connecting to the SQL Server and selecting the tables then using the join dialog?
If so, you won't be able to do the join as you must join on specific fields in the dataset.
My advice would be to use the Custom SQL option in the tableau connection dialog and create the join in there as per my previous example.
- Setup a view in SQL server, perform the join in there and connect tableau directly to the view.
- Setup a view for each table you are trying to join selecting all columns then add a new column which calls the format function. Then you could reference these two views in Tableau and join on the two new format fields.
If you're copying the data from the MS SQL database into Excel (as per your first post example), I would suggest either custom SQL or adding a similar format column manually into each sheet in Excel.
Thank you Tom.
Your post has given us enough ideas to come up with solution.