Joining data from different tables with no common fields

[hay_001]

Hello,

I'm trying to join the two tables below, is there a way to join both tables with no common field? I want to join using Referral code and Key. All suggestions are welcome. Both tables does not have anything in common. Example would be join a value ("XYQA") in table 1 with another value in table 2("abcds")

Example below

Table 1
Referral Code	Description Name
BMWM2	bmw m2
BMWM3	bmw m3
BMWM4	bmw m4
BMWM5	bmw m5
BMWM6	bmw m6
BMWM7	bmw m7
BMWM8	bmw m8

Table 2
Key	Database Data
abc1	ABC1 Ja
dba12	Dba12
rocks	Rocks
atl	Atlanta works
nyc	New York data
bh1212	bh data
pp8	pp8 logs

Result 

Referral Code	Description Name	Key	Database Data
BMWM2	bmw m2	abc1	ABC1 Ja
BMWM3	bmw m3	dba12	Dba12
BMWM4	bmw m4	rocks	Rocks
BMWM5	bmw m5	atl	Atlanta works
BMWM6	bmw m6	nyc	New York data
BMWM7	bmw m7	bh1212	bh data
BMWM8	bmw m8	pp8	pp8 logs

[MeganBowers]

Hi @hay_001 ,

You can use a join and select the "join by record position" radio button and the records will be placed side by side, so this should work as long as you have the tables sorted in the exact order you would like them to be in the final table.

 

[NataliaElias]

Hi, 

You can use the Append Fields tool for this. 

Best, 
Natalia

[Emmanuel_G]

Hi @hay_001 ,

Find attached three ways to do this :

[hay_001]

Thank you all for your suggestions. I have tried all the above solutions, but this is where i'm still having an issue.

Table 1 has over hundreds of rows and Table 2 has another two thousand of rows with different values, so when i use a join or append, is not the exact result.

[MeganBowers]

@hay_001 I would say then you need to create a mapping field of some sort. It's a bit hard to say how without having the exact dataset, but if you want certain rows next to other certain rows, you need to add a key field that is the same for the rows you want to line up, and then you can join on that field.

[Emmanuel_G]

@hay_001 ,

Given the size of the files and without a dataset available, it is not easy to imagine a solution that could help.

Is it possible to provide at least samples from each of your two datasets?

[hay_001]

@Emmanuel_G I attached a test dataset with mapping of the keys

[rohit782192]

Any solution for these.?

[anni6shah]

Hello, 

You could add a Record ID to both datasets and join on Record ID, then union the extra data to the bottom of the joined data (this will be the extra data from the dataset with more rows than the other). Then remove the Record ID fields.