Joining 2 tables with no common field/ i created a field mapping

[hay_001]

Hello,

I tried joining two tables with no common field, but could not figure it out.

Both tables has over hundreds of records. I have also attached a sample file with different tabs of data.

Mapping of fields

Name_id	Car_id
AIM21	aaims
AO21	all
AR21	ro
ATL21	atl
BAY21
BH22	bh
BUR21	bhdc
CC820
CHV21	eys
CLR21	stone
CO21	idad
CPT2021	copton
CPT21	den

Table 1

Id	Name_id	Value	Location
5e-3b68638bd140	AIM21	SERS	New York
3r5-46788	CPT2021	Capt	Germany
343678	CLR21	Rect	Norway
281930	CC820	CRID	India

Table 2

Car_id	Qty	Year
aaims	2	1/15/2022
copton	5	11/19/2019
stone	10	10/1/2021
	3	9/10/2020

Expected Result

Id	Name_id	Value	Location	Car_id	Qty	Year
5e-3b68638bd140	AIM21	AIMSERS	New York	aaims	2	1/15/2022
3r5-46788	CPT2021	Capt	Germany	copton	5	11/19/2019
343678	CLR21	Rect	Norway	stone	10	10/1/2021
281930	CC820	CRID	India		3	9/10/2020

[binu_acs]

@hay_001 if you select join by position option you will get the desired result

[hay_001]

@binu_acs Thanks, but join with position option won't work. Both tables have over hundreds of records. 

[davidskaife]

Hi @hay_001 

Here is a way of doing it, add the field mapping to Table 1, so that stream has the corrisponding value for Car_ID in Table 2, then join on Car_ID:

[binu_acs · Accepted solution]

@hay_001 in that case you can use the multiple join tool to get the desired result

[hay_001]

@binu_acsThanks, that worked. That was a lifesaver.

[hay_001]

@binu_acs That worked.. I appreciate both help..

[WesHarden]

I had the same problem until I found the tool append fields.