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.

Joining data from different tables with no common fields

hay_001
7 - Meteor

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
BMWM2bmw m2
BMWM3bmw m3
BMWM4bmw m4
BMWM5bmw m5
BMWM6bmw m6
BMWM7bmw m7
BMWM8bmw m8

 

 

Table 2 
  
KeyDatabase Data
abc1ABC1 Ja
dba12Dba12
rocksRocks
atlAtlanta works
nycNew York data
bh1212bh data 
pp8pp8 logs

 

 

Result 

 

Referral Code Description NameKeyDatabase Data
BMWM2bmw m2abc1ABC1 Ja
BMWM3bmw m3dba12Dba12
BMWM4bmw m4rocksRocks
BMWM5bmw m5atlAtlanta works
BMWM6bmw m6nycNew York data
BMWM7bmw m7bh1212bh data 
BMWM8bmw m8pp8pp8 logs

 

7 REPLIES 7
MeganDibble
Alteryx Community Team
Alteryx Community Team

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.

Screen Shot 2022-08-05 at 8.52.47 AM.png

NataliaElias
7 - Meteor

Hi, 

You can use the Append Fields tool for this. 

 

Best, 
Natalia

Emmanuel_G
13 - Pulsar

Hi @hay_001 ,

 

Find attached three ways to do this :

Emmanuel_G_0-1659711699344.png

 

hay_001
7 - Meteor

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.

MeganDibble
Alteryx Community Team
Alteryx Community Team

@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
13 - Pulsar

@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
7 - Meteor

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

Labels