topic Re: How Load tables like SQL minus in QlikView

How Load tables like SQL minus

Tue, 23 Mar 2010 17:17:03 GMT

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

How Load tables like SQL minus

Anonymous — Tue, 23 Mar 2010 17:39:34 GMT

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;

Re: How Load tables like SQL minus

Miguel_Angel_Baeyens — Tue, 23 Mar 2010 17:44:07 GMT

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.

How Load tables like SQL minus

Tue, 23 Mar 2010 19:40:31 GMT

Thank you so much... it worked

How Load tables like SQL minus

Anonymous — Sat, 22 Oct 2011 06:16:27 GMT

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

Re: How Load tables like SQL minus

Miguel_Angel_Baeyens — Sat, 22 Oct 2011 11:27:19 GMT

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