table1 MINUS table2 sql

Fri, 28 May 2010 10:56:10 GMT

Hi! I'm woking with two tables, table1 and table2 like the following ones:

Expediente:

LOAD "No_" as nexpediente ; SQL SELECT "Cód_ Expediente" FROM table1;

Incidencia:

LOAD "Cód_ Expediente" as nexpediente, "No_ Incidencia" as nincidencia; SQL SELECT "Cód_ Expediente", "No_ Incidencia" FROM table2;

I just want to take every single nexpediente from table1 which doens't exists on table2.

I've tried with a simple sql sentece like:

select exp from table1;

MINUS

select exp from table 2;

Following this sentence qlikview does not show up any error but it's like it doesn't recognize MINUS since the results are not the expected.

I also tried with another simple sentece of sql like:

select exp from table 1

where exp not in (select exp from table2);

But it doesn't work either....

I've also seen this post (http://community.qlik.com/forums/p/27092/103702.aspx#103702) at the forum about this same question, but I've tried what they explain and it doesn't work either...

if someone could help me please...thx!

Fri, 28 May 2010 15:44:03 GMT

The minus is not known to QV, you would need to do it in the SQL query assuming your DB has this option. If not change the loading of your data:

Within QV load first table 2, then on loading table 1 check with exists() that you do not want it. Drop table 2 at the end.

Look at the exists function in book 1 page 414

Regards

Juerg

topic table1 MINUS table2 sql in QlikView