community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

join with transpose

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 IDPurchase 1Purchase 2Purchase 3 Purchase 4
Client 1Product AProduct BProduct CProduct D
Client 2Product EProduct FProduct GProduct A
Client 3Product IProduct JProduct KProduct L
Client 4Product HProduct IProduct K 
Client 5Product JProduct NProduct BProduct N
Client 6Product OProduct PProduct Q 
Client 7Product SProduct T  

 

Product SupplierPrice
Product   ASupplier A150
Product   BSupplier B200
Product   CSupplier C300
Product   DSupplier D366
Product   ESupplier E441
Product   FSupplier F516
Product   GSupplier G592
Product   HSupplier H667
Product   ISupplier I742
Product   JSupplier J817
Product   KSupplier K892
Product   LSupplier L967
Product   MSupplier M80
Product   NSupplier N180
Product   OSupplier O280
Product   PSupplier P380
Product   QSupplier Q480
Product   RSupplier R580
Product   SSupplier S680
Product   TSupplier T780

 

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

 

Client Product SupplierPrice
Client 1Product ASupplier A150
Client 1Product BSupplier B200
Client 1Product CSupplier C300
Client 1Product DSupplier D366
Client 2Product ESupplier E441
Client 2Product FSupplier F516
Client 2Product GSupplier G592
Client 2Product ASupplier A150
Client 3Product ISupplier I742
Client 3Product JSupplier J817
Client 3Product KSupplier K892
Client 3Product LSupplier L967

 

Tank you for your help and reply

  

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@DataScientist ,

 

I've joined with transpose for you as requested:

 

capture.jpg

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels