How Load tables like SQL minus

[#1]

HI experts,

I need your help ... I need the information than exist one table but doesn't exist to another table..Like the MINUS query at SQL than returns all rows in the first query that are not returned in the second query.

I write an example:

Table Store_Information

store_name	Sales	Date
Los Angeles	1500 €	05-Jan-1999
San Diego	250 €	07-Jan-1999
Los Angeles	300 €	08-Jan-1999
Boston	700 €	08-Jan-1999

Table Internet_Sales

Date	Sales
07-Jan-1999	250 €
10-Jan-1999	535 €
11-Jan-1999	320 €
12-Jan-1999	750 €

SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales

Result:

Date

05-Jan-1999

08-Jan-1999

[Anonymous]

Try this:

table1:
SQL SELECT Date FROM Internet_Sales;

table2:
LOAD Date
WHERE not exists(Date);
SQL SELECT Date FROM Store_Information;

DROP TABLE table1;

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

It occurs to me a couple of solutions:

First is load your table with that specific query passed on to your sql server, guessing (untested)

Table: 
LOAD Field1, 
     Field2, 
     Field3; 
SQL SELECT Date 
FROM Store_Information 
MINUS SELECT Date 
FROM Internet_Sales;

Second is create a map of those dates which do appear in the firs table, and using them as a filter to load all values apart from them coded kind of

ExceptionsMap:
MAPPING LOAD Date, 
     1 AS Flag; 
SQL SELECT Date 
FROM Internet_Sales;

and then

TableMinus:
LOAD store_name, 
     Sales, 
     Date 
WHERE ApplyMap('ExceptionsMap', Date, 'No') = 'No'; 
SQL SELECT store_name, Sales, Date 
FROM Store_Information;

Of course, field names may vary, and I used your example to clarify. Hope that helps!

EDIT: Although both should work, I posted before reading Michael's answer, which is clearer and smarter.

[#4]

Thank you so much... it worked

[Anonymous]

I Have Excel Data Only...not sqlserver Data..then how to use minus

[Miguel_Angel_Baeyens]

Hi,

The logic is exactly the same, although you will have to use the button "Table Files" instead of the SQL statement to build your script.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica