topic Re: match for email address from different tables in QlikView

match for email address from different tables

renjithpl — Wed, 24 Aug 2011 09:31:49 GMT

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

Re: match for email address from different tables

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 09:38:43 GMT

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

match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 09:43:46 GMT

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

Re: match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 09:52:45 GMT

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

Re: match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 09:55:22 GMT

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

Re: match for email address from different tables

renjithpl — Wed, 24 Aug 2011 10:24:55 GMT

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

Re: match for email address from different tables

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 10:33:25 GMT

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

Re: match for email address from different tables

renjithpl — Wed, 24 Aug 2011 10:56:15 GMT

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

Re: match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 11:07:40 GMT

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

Re: match for email address from different tables

renjithpl — Wed, 24 Aug 2011 11:12:00 GMT

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

Re: match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 11:17:17 GMT

Hi,

It is because null values so for that you need to put some coding for that column like

=isnull ( EMAIL ), 'No' , 'Yes'

Regards

Anand

Re: match for email address from different tables

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 15:29:29 GMT

Renjith,

Check the application attached. I don't see any "null" value even when there is no corresponding address (in that case the result is "No"). Am I missing something?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: match for email address from different tables

its_anandrjs — Wed, 24 Aug 2011 15:34:25 GMT

Hi,

See the sample file for the clearity of the is it a solution for you.

Regards

Anand