4 Replies Latest reply on May 20, 2014 1:31 PM by Michel Cavas

    Joining on Year and Month while omitting day

    Michel Cavas

      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!

        • 1. Re: Joining on Year and Month while omitting day
          Tom W

          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')
          
          1 of 1 people found this helpful
          • 2. Re: Joining on Year and Month while omitting day
            Michel Cavas

            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?

            • 3. Re: Joining on Year and Month while omitting day
              Tom W

              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.

              • 4. Re: Joining on Year and Month while omitting day
                Michel Cavas

                Thank you Tom.

                Your post has given us enough ideas to come up with solution.