match for email address from different tables

[renjithpl]

Hi All,

I have two different table loaded in my application, both tables have no connection

Below is my first table

Matter ID	Name of the Matter	Email ID of Notice Recipient
447237	DERIPASKA, OLEG VLADIMIROVICH	William.Bozza@hotmail.com
447237	DERIPASKA, OLEG VLADIMIROVICH	alexandra.kogan@hotmail.com

below is my second table

manager_name	Email Address	Job_Title
James E Staley	abbbb@gmail.com	Executive
Barry L Zubrow	abcd@gmail.com	Risk Methodology Executive
James E Staley	William.Bozza@hotmail.com	Advisory Executive
Frank J Bisignano	michael.j.ahearn@jpmchase.com	HR Bus Partner - Executive

I want to write a code, like =if ([Email ID of Notice Recipient] = [Email Address], 'Yes', 'No')

Basically my second table has a directory of email ids, so if atleast one email address matches from table one with the table two [Email Address], then it should say yes else no.

Regards

Renjith

[Miguel_Angel_Baeyens]

Renjith,

What is the link between the first and second table? I'm thinking of a mapping table and the ApplyMap() function. Or it might it do just using EXISTS() as of the following (non in a record per record basis)?

Table1:
LOAD "Matter ID",
     "Name of the Matter",
     "Email ID of Notice Recipient"
FROM ...;
Table2:
LOAD manager_name,
     "Email Address",
     Job_Title,
     If(EXISTS("Email ID of Notice Recipient", "Email Address"), 'Yes', 'No') AS Code
FROM ...

The "Code" field in Table2 will be populated with "Yes" when the email address in the record exists in any of the records in Table1, and with "No" if it doesn't.

Is that what you are looking for?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[its_anandrjs]

Hi,

As i under stand you need to create another column as flag column which checks the requirement as you say =if ([Email ID of Notice Recipient] = [Email Address], 'Yes', 'No') as Flag Column but table should connect with each other with email id column .

Reagrds,

Anand

[its_anandrjs]

Hi,

You need to write so code like the below this for two tables

Table1:

LOAD [Matter ID],

     [Name of the Matter],

     [Email ID of Notice Recipient]

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet4);

Table2:

LOAD manager_name,

     [Email Address],

     Job_Title,

     If(Exists([Email ID of Notice Recipient], [Email Address]), 'Yes', 'No') AS Available

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet5);

Regards,

Anand

[its_anandrjs]

Hi,

If you want those data which are only available in this two tables then you need to put

Where exists([Email ID of Notice Recipient], [Email Address]);

in load statement after with above code.

Regards,

Anand

[renjithpl]

so how will i get yes or no in a pivot table or straight table..

i am looking a output like this

Matter ID	Date and Time Received	Total Number of Recipients	=subfield([Email ID of Notice Recipient], '@', 1)	Available
444719	8/16/2011 1:50:00 . PM	1	heather.k.emmert	Yes

[Miguel_Angel_Baeyens]

Renjith, did you change your script as suggested above using the EXISTS() function?

[renjithpl]

yes i corrected... and in my pivot table i assigned Code but its not giving the correct answer..

[its_anandrjs]

Hi,

Yes you are able to make a pivot table or straight table for this you need to conditionally load the data or if it is only for availbale = Yes then sort data according to that data.

Regards

Anand

[renjithpl]

It works fine, except, if the email doesnt exist it shows '-', its not showing 'No'