3 Replies Latest reply on Dec 23, 2013 8:34 AM by Jonathan Drummey

    join or blend tables

    Jean-Sébastien Vincent

      My database has two tables :

       

      First tables list employees :

       

      Table 1 : employees
      Employee_nrLast_nameFirst_name
      1SmithJohn
      2RooneyTim
      3HutchinsonFrank

       

      Second table list salaries. In this table a row is added each time an employees table is adjusted. The table looks like this :

      Table 2 : salary
      Employee_nrSalaryDate_update
      110001/01/2012
      212002/01/2012
      311003/01/2012
      110501/06/2012
      311501/07/2012
      110801/09/2012
      212501/05/2013

       

      How can I generate a report which gives me the most recent salary per employee. This report should look like this :

      Report
      Employee_nrLast_nameFirst_nameLast salary
      1SmithJohn1080
      2RooneyTim1250
      3HutchinsonFrank1150

       

       

      Many thanks,

        • 1. Re: join or blend tables
          kettan

          Welcome to the forum Jean-Sébastien!

           

          I don't think this is solved by joining or blending tables, but rather with a custom SQL subquery outside the workbook or a table calculation inside the workbook. I think this table calculation works:

          .

          WINDOW_MAX(MAX([Date_update])) = MAX([Date_update])
          
          
          
          
          
          

          .

          Jóhan aka Kettan

           

          Ps. See more in attached workbook and print screen below.

           

          Ps. If this was the correct answer, the proper forum behavior is to mark it as Correct.

           

          thread 137017 join or blend tables.png

          • 2. Re: join or blend tables
            Aaron Clancy

            This is 6 one half a dozen the other but I'm all about concise code:

             

            If you do everything the same as Johan above your calc can simply be:

             

            last()

             

            When you put that calc on the filters shelf, set your proper "compute using" and set to zero it will have the same behavior as your date dimension automatically sorts ascending.

            Screen Shot 2013-12-22 at 8.08.11 PM.png

            • 3. Re: join or blend tables
              Jonathan Drummey

              Nice suggestion, Aaron, and I'm glad to see you taking on a table calc problem, Johan!

               

              Here's a tip for even more conciseness:

               

              Keep the number of dimensions in the view to the minimum performance for accurate results. In this case with Employee_Num in the view, Last_Name and First_Name don't have to be dimensions. They can be aggregated with MIN(), MAX(), or ATTR(), and the Compute Using of the LAST() calc set to just the Date_Updated.

               

              This has several impacts:

               

              - Tableau is sending a simpler query to the data source that could be faster to return data.

              - Configuring the addressing and partitioning of table calcs is easier, faster, & more accurate because there are fewer dimensions to manipulate.

              - Reviewing the addressing and partitioning of table calcs is faster & more accurate because less text is necessary to describe what's going on.

              - You'll run into fewer problems with unexpected/unwanted densification (domain padding & domain completion behaviors)***.

               

              *** In this view, the Compute Using of LAST() on Date_Updated and the pill arrangement will cause Tableau to perform domain completion, generating extra marks that then get filtered out. Rather than having Tableau do the densification then filter it out, and rather than just hiding the header for Date_Updated, my suggestion is to put the Date_Updated pill on the Level of Detail Shelf. This will prevent the domain completion from happening.

               

              Jonathan