Common values in two fields in two tables

[Saiteja1]

Hi

I have two tables

Table1
ID , Email
1, abc@
2, def@
3, ghi@

Table2
ID , Email
2,def@
3,ghi@
4,jkl@

I want new field which contains the emails which are common in both the tables

Thanks in Advance

[sk88024]

Hope this help!

Script - 

Table1:
LOAD * INLINE [
ID , Email
1, abc@
2, def@
3, ghi@
];

Table2:
LOAD * INLINE [
Id, EMAIL
2,def@
3,ghi@
4,jkl@
];

CheckEmail:
LOAD ID as Final_ID,
Email as Final_Email_1
Resident Table1;

Join
load Id as Final_ID,
EMAIL as Final_Email_2
Resident Table2;

Left Join
load Final_ID, If(Final_Email_1 = Final_Email_2, Final_Email_1, 'Email ID not matched') as [Final Email]
Resident CheckEmail;

Final_Data:
NoConcatenate
LOAD [Final Email] as [Email ID] Resident CheckEmail where [Final Email] <> 'Email ID not matched';

Please note that I have not dropped any field or table for the presentation purpose.