Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Effective way of joining 3 tables

michellekim
6 - Meteoroid

Hi all

 

I'm trying to join below three tables on matching on different fields. 

For example below... 

Is there a easier way of joining the three tables to get the sum price per basket (in table 1)? I was using 'Join' but I didn't like the idea of using multiple joins..

Table 1   Table 2  Table 3  
Grocery ListPurchase dateFruit NameVegitable NameFruit NamePricePrice Effective DateVegitable NamePricePrice Effective Date
Basket A5/03/2000BannaCarrotApple31/01/2000Carrot31/01/2000
Basket B5/05/2001Apple Apple35/10/2001Carrot35/10/2001
Basket C3/05/2002 CarrotApple34/05/2002Carrot34/05/2002
    Banna41/01/2000   
    Banna45/10/2001   
    Banna44/05/2002   
          

 

Thanks in advance

7 REPLIES 7
mst3k
11 - Bolide

multiple joins are the way if you are joining by different fields. the multiple join tool only works if the join fields are the same between all the tables. if you only have 3 tables, just do regular joins. if there's hundreds of joins you need to make for some reason, you could build a macro

Qiu
20 - Arcturus
20 - Arcturus

@michellekim 
I am sorry but I dont think can avoid using join here, if not multiple.
I use one join, and also notice there is trick here about the effective price date. Hope my understanding is correct.

0915-michellekim.PNG

michellekim
6 - Meteoroid

Brilliant how did I not think about that 😄 

Thanks Castor

michellekim
6 - Meteoroid

Thank you for the tips

Qiu
20 - Arcturus
20 - Arcturus

@michellekim 
Glad I can help.

I am thinking to submite this top as an idea of weekly challenge and hope you are fine with it.

 

michellekim
6 - Meteoroid

@Qiu Yup! I'm happy with you sharing 😄  

Qiu
20 - Arcturus
20 - Arcturus

@michellekim 
Thank you for agreeing with it. 😁

Labels