3 Replies Latest reply on Nov 11, 2018 4:04 AM by Balaji Subramani

    Join tables using calculation

    Balaji Subramani

      Hi

       

      I have two tables A and B with dates. I want to join two tables with a monthly columns

      Ex. Table A have 2 years of data and Table B have 3 years of data.

      Now I don't want to join A and B directly with its dates.

       

      I want to join Table A -> Jan -2018 with Table B-> Jan -2018

      Not with direct dates. Can i join two tables with their calculated fields?

      Any help appreciated

        • 1. Re: Join tables using calculation
          Akram Ebrahim

          Hi Subramani,

           

          You can join tables A and B together using a calculated field. Which version of tableau you are using?

          I believe this option available in tableau desktop 10 version from long back.

           

          While joining the tables select Create join calculation

           

           

          You can join your tables as monthly aggregates? its bit confusing. To create Jan-2018 you have to write datetrunc calculation

           

          Ex: Monthly aggregate calculation

           

          DATETRUNC('month',[date])

           

          Let me know with this steps, you can solve the problem or not?

          2 of 2 people found this helpful
          • 2. Re: Join tables using calculation
            Ritesh Bisht

            Hi Balaji,

             

            Yes possible.

            I have explained the same @ my video blog below Joins with Troubleshooting Tableau Part 2 - YouTube

             

             

            Or you can read the blog below

             

             

            Date mismatch

            A common mismatch scenario when working with date data occurs when the date values in one field of the join condition are captured at a different level of detail than the other field in the join condition. In this case you can use a calculation in the join condition to change the format of the field on one side of the join condition so that its format matches the other field in the join condition.

            For example, suppose you have the following two tables of data:

            Projector rentalPatron
            DATERESERVATION TYPEREQUESTER ID

            1/1/2000

            Individual233445589
            1/28/2002K-12365948999
            1/29/2002Non-profit233448888
            12/5/2002K-12365948999
            5/5/2003Non-profit334015476
            3/12/2004

            Non-profit

            334015476
            3/15/2006City211896980
            7/8/2007K-12334015476
            1/4/2008Individual560495523
            3/8/2009Non-profit233445566
            2/14/2014

            Non-profit

            233445566
            12/21/2015Non-profit233445566
            2/10/2016Non-profit233445566
            IDFIRST NAMELAST NAMEBRANCHMEMBER SINCEUNITS BORROWEDFEESSUGGESTED LIMIT
            454613981AdamDavisWest201225010
            232502870Alan

            Wang

            North20001015
            298000916AmandaSmithNorth2001546.415
            233448978AndrewSmithNorth2000363.5015
            233445566AshleyGarciaSouth200024311.3015
            900005122BrianFrankEast201112.1010
            921491769ElizabethJohnsonWest201019.510
            233445589FredSuzukiNorth200052.9015
            344556677HenryWilsonSouth20053.215
            939502870JaneJohnsonWest20170010

            To find out more information about new patron behavior, joining the Patron table to the Projector Rental table might provide some insight about which library services motivate new memberships. The common fields between the two tables appear to be "Date" and "Member since." However, the date values in each field are captured at different levels of detail. To join these tables on their respective date fields, use a combination of DATE functions in a calculation on each side of the join condition to make the level of detail in each field match.

            Using the calculation in the join produces the following combined table:

            DATERESERVATION TYPEREQUESTER IDIDFIRST NAMELAST NAMEBRANCHMEMBER SINCEUNITS BORROWEDFEESSUGGESTED LIMIT

            1/1/2000

            Individual233445589232502870AlanWang

            North

            200010.0015
            1/1/2000Individual233445589233445589FredSuzukiNorth2000520.9015
            1/1/2000Individual233445589233445566AshleyGarciaSouth200024311.3015
            1/1/2000Individual233445589233448978AndrewSmithNorth2000363.5015

            To determine if a patron rented the projector in the same year he or she started his or her membership, add one more clause to the join based on ID.

            The result of the additional join condition shows that only one patron might have started his membership to rent a projector.

            DATERESERVATION TYPEREQUESTER IDIDFIRST NAMELAST NAMEBRANCHJOINEDUNITS BORROWEDFEESSUGGESTED LIMIT
            1/1/2000Individual233445589233445589FredSuzukiNorth2000520.9015

            Hope it helps !

             

            Thanks,

            Ritesh

            Please mark the answer as CORRECT & HELPFUL if it really helps you so that it can help others as well

            2 of 2 people found this helpful
            • 3. Re: Join tables using calculation
              Balaji Subramani

              Thank you Ritesh and Akram! It was really helpful!