topic Re: how to create a table from two excel sheets that only shows the people who are on BOTH excel sheets in QlikView

how to create a table from two excel sheets that only shows the people who are on BOTH excel sheets

Fri, 03 Aug 2012 16:13:42 GMT

Hello,

I have two excel files which are attendee lists - company name, first name, last name, email. What i want to do is create a table that shows the attendees that are on BOTH lists. So if attendee John smith is on list A and B, he should show up in the table. If he is only on list A i do not want him to show up. It's pretty simple but i don't know what to do. Is it an expression? Would appreciate the help asap if possible!

Re: how to create a table from two excel sheets that only shows the people who are on BOTH excel sheets

rajni_batra — Mon, 06 Aug 2012 07:50:26 GMT

Use inner join while loading the tables, it will show u common values.

Hope it helps!!!

Re: how to create a table from two excel sheets that only shows the people who are on BOTH excel sheets

Miguel_Angel_Baeyens — Mon, 06 Aug 2012 07:52:27 GMT

Hi,

The script could be something like this:

NamesInFirstTable:
LOAD [First Name] & '/' & [Last Name] & '/' & Company AS Key
FROM 
[svp master list.xls]
(biff, embedded labels, table is Sheet1$);
Directory;
LOAD Company,
     [First Name],
     [Last Name],
     [Contact Title],
     Email
FROM
[svp master list.xls]
(biff, embedded labels, table is Sheet1$);
Directory;
LOAD Company,
     [First Name],
     [Last Name],
     [Contact Title],
     Email
FROM
[svp master list2.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE EXISTS(Key, [First Name] & '/' & [Last Name] & '/' & Company);
// The EXISTS() will load only those that are present in Table1
DROP TABLE NamesInFirstTable;

Hope that helps.

Miguel