In Tableau I am trying to join two SQL-server tables which are updated on different frequencies. One table contains production data which is updated on a shiftly basis, meaning the table has 3 entries per day per product per machine. The other table contains targets per product per machine, but these targets are set on the month level.
The idea is that the running sum of the production data table is compared to the target table. Both tables have a date field, machine field and a product field. Is it possible to join these two tables on year and month of the date field, where day part of the date gets omitted?(And of course a join on machine and product as well)
Attached an example excel file, with simplified the production and target table. The production example data is on day level, not shift level, but I think for the purpose of this example it does not influence it.
Any help in this matter is greatly appreciated!
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.
Alternatives;
- 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.
