join with transpose

[DataScientist]

Hi,

I have a problematic that I could not solve

Suppose we have data base with a list of client and their purchases, and an other data base with the list of product and their respective attributes as below :

Client ID	Purchase 1	Purchase 2	Purchase 3	Purchase 4
Client 1	Product A	Product B	Product C	Product D
Client 2	Product E	Product F	Product G	Product A
Client 3	Product I	Product J	Product K	Product L
Client 4	Product H	Product I	Product K
Client 5	Product J	Product N	Product B	Product N
Client 6	Product O	Product P	Product Q
Client 7	Product S	Product T

Product	Supplier	Price
Product   A	Supplier A	150
Product   B	Supplier B	200
Product   C	Supplier C	300
Product   D	Supplier D	366
Product   E	Supplier E	441
Product   F	Supplier F	516
Product   G	Supplier G	592
Product   H	Supplier H	667
Product   I	Supplier I	742
Product   J	Supplier J	817
Product   K	Supplier K	892
Product   L	Supplier L	967
Product   M	Supplier M	80
Product   N	Supplier N	180
Product   O	Supplier O	280
Product   P	Supplier P	380
Product   Q	Supplier Q	480
Product   R	Supplier R	580
Product   S	Supplier S	680
Product   T	Supplier T	780

I'm looking for result displaying Client list with their purchases with products attributes as below :

Client	Product	Supplier	Price
Client 1	Product A	Supplier A	150
Client 1	Product B	Supplier B	200
Client 1	Product C	Supplier C	300
Client 1	Product D	Supplier D	366
Client 2	Product E	Supplier E	441
Client 2	Product F	Supplier F	516
Client 2	Product G	Supplier G	592
Client 2	Product A	Supplier A	150
Client 3	Product I	Supplier I	742
Client 3	Product J	Supplier J	817
Client 3	Product K	Supplier K	892
Client 3	Product L	Supplier L	967

Tank you for your help and reply

[MarqueeCrew · Accepted solution]

@DataScientist ,

I've joined with transpose for you as requested:

The data in table 2 contained non-whitespace characters.  They were interfering with the join, so I replaced them with a space.  I did get more join results than you have in your table.  As an example, Client 2 has a product A purchase that isn't contained in your results.  I hope that this workflow helps to solve your challenge.

Cheers,

Mark