One to many: Joining a transaction table to a territory lookup table

[jmorris24]

Hello,

I have a transaction table that has the following data

Transaction ID	Amount	Country	Type
1	55	USA	software
2	100	USA	software
3	300	Germany	software
4	500	Switzerland	software
4	250	Switzerland	hardware
5	600	Japan	software
5	300	Japan	hardware
6	500	USA	software
7	300	Japan	software

Then I have a lookup table:

Territory ID	Country	Region
1	USA	AMER
2	Germany	EMEA
3	Switzerland	EMEA
4	Japan	APAC
5	China	APAC

I need to add a column to the main table (transaction table) data called Region. When I go to join the lookup table to add Region in by State I go from having around 3k records in the main table to then having 10k records in the join table. I know why this is but I can't find any information on what to do about it. 

I need the main table to have the same number of rows it started with just with the region add in.

Please help...

[AngelosPachis · Accepted solution]

Hi @jmorris24 ,

Can you try using a Find/Replace tool instead and select to append the records field as shown below?

[jmorris24]

Thank you. Thank you. Thank you. I should have ask 2 hours earlier. That was really easy. 

[AngelosPachis]

I wasn't 100% sure that it will work so we both learned something new 🙂

Thanks for asking @jmorris24

[AIChudnovsky]

When you join to the lookup table, the field you're joining from the lookup table needs to have unique records, otherwise it will join every record that matches and thereby multiplying the amount of output records for each duplicate entry in the lookup table.  Whereas Excel's vlookup function only displays the first match, Alteryx will display all matching records.  A quick way to solve for this is to add a Unique Tool to your lookup table before the Join Tool.

[jmorris24]

My lookup table was as unique as it could have been already. The solution suggested above worked great for my use case. You can't always make things unqiue. sometimes you need the data that is there. 

Cheers!