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 Name||Book Name 2||Sales ($)|
|Sky Blue||Sky Bleu||32432|
|Feather||feathers at night||3445|
|war and love||war and love||4324|
|the end||the endings||3233|
|love and live|
amar y vivir
|hello you||hello! you!||34534|
Data Set B:
|Sky Blue||A.J. Smith||Thriller|
|feathers at night||A.J. Smith||Romance|
|cuentos infantiles||I.L Tailor||Kids|
|the end||K. Jenkins||Mystery|
|amar y vivir||E. Via||Romance|
|hello you||E.L. Trail||Comedy|
|war and love||FL. Sabzak||Romance|
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.