5 Replies Latest reply on Dec 5, 2018 12:30 AM by Chris McClellan

    Join Data with Two Table

    Hanye Lin

      I have two table below´╝Ü

       

      DateBEIJING TEMPSHANGHAI TEMPWUHAN TEMPHARBIN TEMP
      2018.1.1-325-10
      2018.1.2-2-16-11
      2018.1.3003-12
      2018.1.4-60-1-10
      2018.1.5-50-2-14
      ...
      ...

       

      Based on a parameter, I am able to choose any days temperature, for example, the ticker is 1st Jan

      Temperature
      Beijing
      Shanghai
      Wuhan
      Harbin

       

      How I can link these two table and filled in the temperature on 1st Jan into second table?

      Thanks

        • 1. Re: Join Data with Two Table
          Mahfooj Khan

          Hi,

           

          You need to work on your data structure,

          1. Transpose your measures from columns to rows. You need to pivot your data. You may get several links for pivoting. Post pivot perform below steps

          2. Extract Territory information from the pivot field names. You've to remove TEMP word from "BEIJING TEMP".

          3. Rename the pivot field value as "TEMPERATURE"

           

          I hope in this way you can easily get the desired output and you don't have to join with any other table.

           

          Give a try and let us know if this work.

           

          Mahfooj

          • 2. Re: Join Data with Two Table
            Hanye Lin

            Because it's company file I cannot post it but I have 100+ lines to deal with not just this simple table. Therefore, just changing the title is impossible for me and the solution for me can only be doing transposed in tableau itself which calculation etc might helps. However, just need to figure out how to do it.

            • 3. Re: Join Data with Two Table
              Mahfooj Khan

              Hi,

               

              Find the steps below,

               

              Step 1: Connect your source and pivot on your fields like this

              Step 2: Delete the invalid fields

              Step 3. Rename the Pivot field names and values. Once done your data is ready

              Step 4: Now remove the TEMP word using REPLACE(). Go to worksheet and create a calculated field using below logic

              TRIM(REPLACE([Territory],"Temp",""))

               

              Step 5: Now, drag the fields in canvas

              If you still have to join with other data source then you may do like this

              Let us know if this help. Sample workbook attached for your reference.

               

              Mahfooj

              • 4. Re: Join Data with Two Table
                Hanye Lin

                Thanks for that, it helps a lot

                • 5. Re: Join Data with Two Table
                  Chris McClellan

                  Do you have to use a parameter for another reason ?

                   

                  I've done this just with the data you provided, in the format that you provided:

                   

                  2018-12-05 19_29_14-Tableau - Book1.png