topic Re: Expressions equivalent to SQL NOT IN in QlikView

Expressions equivalent to SQL NOT IN

Wed, 06 Nov 2013 16:36:38 GMT

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

Re: Expressions equivalent to SQL NOT IN

Gysbert_Wassenaar — Wed, 06 Nov 2013 16:44:49 GMT

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')

Re: Expressions equivalent to SQL NOT IN

Wed, 06 Nov 2013 16:49:07 GMT

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

Re: Expressions equivalent to SQL NOT IN

Gysbert_Wassenaar — Wed, 06 Nov 2013 17:05:57 GMT

Temp:

Load Name from Customer_Details where Age > 20;

Result:

noconcatenate

Load Name from Customer where not exists(Customer);

Drop Table Temp;