2 Replies Latest reply on Jul 8, 2018 6:48 PM by Kenneth Lam

    Create a master key column from 3 fields for joining tables

    Kenneth Lam

      Hi all,

       

      I have got three tables, each with one column, that contain brand list, country list and time (week). To join with my other data tables, I would like to create a cross-product of the three fields which serve as a unique key.

      I have tried using pivot and joins but no luck so far. I have attached the workbook and a screencap below. Please help/ advise.

      The merged list should look something like:

         

      RowCountryWeek Start DateBrand
      1Albania1/2/2017A
      2Albania1/2/2017B
      3Albania1/2/2017C
      4Albania1/9/2017A
      5Albania1/9/2017B
      6Albania1/9/2017C
      7Albania1/16/2017A
      8Albania1/16/2017B
      9Albania1/16/2017C
      10Australia1/2/2017A
      11Australia1/2/2017B
      12Australia1/2/2017C
      13Australia1/9/2017A
      14Australia1/9/2017B
      15Australia1/9/2017C
      16Australia1/16/2017A
      17Australia1/16/2017B
      18Australia1/16/2017C
      19China1/2/2017A
      20China1/2/2017B
      21China1/2/2017C
      22China1/9/2017A
      23China1/9/2017B
      24China1/9/2017C
      25China1/16/2017A
      26China1/16/2017B
      27China1/16/2017C
        • 1. Re: Create a master key column from 3 fields for joining tables
          Joshua Milligan

          Hi Kenneth,

           

          If I understand what you are trying to do (a cross join to get all possible combinations of brands, weeks, and countries), then this is how I would approach it.

           

          • For each input, add a calculated field.  I've named mine Join and the code is the single hard coded integer 1.  This will be used to cross join all the inputs.

          • Then join them together (not union) on the Join field.  That will match every row on one side with every row on the other (a true cross product).  You'll have to accomplish this with two separate joins:
          • You can then remove the Join fields after the join as they are no longer needed

           

          I've attached an extracted Tableau Prep flow file (.tflx) which includes the data I mocked up.  For reference, if you just attach a .tfl file, then no one will be able to use it because it does not include the data (you can save it as that type or export a packaged flow from the File menu).

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Create a master key column from 3 fields for joining tables
            Kenneth Lam

            Thanks Joshua! That helps!

             

            Got it, will do with a packaged flow next time