Expressions equivalent to SQL NOT IN

[#1]

Hi,

Currently all the data is loaded into the QVD. I need to display a table where it only returns records where a particular field isn't in another. In SQL I can use the NOT IN clause. i.e

Select Name from Customer

WHERE Name not in (select Name from Customer where Age > 20)

How can I do this in QlikView? QVD is only one dataset so is it possible to check against itself?

Hope this makes sense.

Thanks

[Gysbert_Wassenaar]

Select Name from Customer

WHERE Name not in (select Name from Customer where Age > 20)

is the same as

Select Name from Customer

WHERE  Age <= 20 OR Age is Null)

The Qlikview equivalent is

Load Name from Customer

Where Age <= 20 OR len(trim(Age))=0;

The syntax for where Name not in ('A','B','C') is where not match(Name,'A','B','C')

[#3]

Hi,

Thanks for the quick response.

The example I gave is not quite right- sorry. 'Not IN clause should've been from a different table. ie

Select Name from Customer

WHERE Name not in (select Name from Customer_Details where Age > 20)

Thanks

[Gysbert_Wassenaar]

Temp:

Load Name from Customer_Details where Age > 20;

Result:

noconcatenate

Load Name from Customer where not exists(Customer);

Drop Table Temp;