6 Replies Latest reply on May 26, 2020 9:37 AM by Jonathan Drummey

    How do I create a join using multiple columns?

    Patricia B

      Hi everyone! I'm new to Tableau Prep and I would really appreciate any help please. I can't use my actual data as it's highly sensitive but here is mock context for my actual problem:

       

      I have data set A which contains book names and a lot of sale metrics (daily sale numbers) and I need to connect this to data set B which includes information of the book e.g.: book names, author, type of book, year of release, etc. I want to add these metrics from Data set B: author, type of book, year of release to data set A using the common field "book name". The issue is, is that there are 2 columns of book names. Book Name: English version and Book name 2: local version/abbreviated version in Data set A which either could relate to "book name" in Data Set B as this includes a variation of the English version and local version.

       

      I want to do a left join using these data sets:

       

      Data Set A:

       

      Book NameBook Name 2Sales ($)
      Sky BlueSky Bleu32432
      Featherfeathers at night3445
      Childhood stories

       

       

      cuentos infantiles

      3244
      war and lovewar and love4324
      the endthe endings3233
      love and live

       

       

      amar y vivir

      5455
      hello youhello! you!34534

       

      Data Set B:

       

      Book Name
      Author
      Genre
      Sky BlueA.J. SmithThriller
      feathers at nightA.J. SmithRomance
      cuentos infantilesI.L TailorKids
      the endK. JenkinsMystery
      amar y vivirE. ViaRomance
      hello youE.L. TrailComedy
      war and loveFL. SabzakRomance

       

      As you can see both Book Name and Book Name 2  in data set A could be joined Book Name in data set B. Does anyone have any suggestions on how to do this so I can tie it all back to one?

       

      Data set A has approx 500,000 lines.

       

      Also, not all book names are in Data set B so I'll need to manually add this information in later.

       

      Someone suggested to give each book name in Data Set B a unique ID and then do 2 joins (one on book name and another on book name 2) and join it back together at the end but I'm not sure if that resolves this as I don't believe that would tie it back to Data Set A. Again, I'm fairly new to Tableau Prep so any help would be great.

       

      Thank you!