Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
nezuko_kamado
Creator
Creator

Finding common values from two columns using different column names when the tables don't have a sharing key column

When finding matching values from coulmns, we often use 'join' or 'left join' using shared column as a key column, and flag the values.

But my case, the column names are different in separated tables but share common values. Also don't have a key column to use 'join'. Alos I don't want to change column name for Object and ID.
How to compare values of columns using  different names when the tables don't have a sharing key column.  
I just simplerfied tables as below, and what I want to have is New Table.

Capture2.PNG

 

table1:
load*inline [Category, Object
A, 101
B, 201
C, 301
D, 401
E, 501
F, 601];

table2:
load*inline [Sector, ID
red, 101
red ,102
blue, 103
yellow, 201
green, 202
purple, 203];

Labels (4)
1 Solution

Accepted Solutions
nezuko_kamado
Creator
Creator
Author

merged:
Load
Object as Key,
Category
resident Table1;

Left Join (merged)
load
ID as Key,
Sector
Resident Table2;

NoConcatenate
newTable:
load *
Resident merged
Where Sector>0 And Category>0;

drop Table Table1;
drop Table Table2;
drop Table merged;

View solution in original post

1 Reply
nezuko_kamado
Creator
Creator
Author

merged:
Load
Object as Key,
Category
resident Table1;

Left Join (merged)
load
ID as Key,
Sector
Resident Table2;

NoConcatenate
newTable:
load *
Resident merged
Where Sector>0 And Category>0;

drop Table Table1;
drop Table Table2;
drop Table merged;