topic Re: QlikView 'Not In' exclude functionality in QlikView

QlikView 'Not In' exclude functionality

andyquirin — Tue, 24 Sep 2013 16:49:10 GMT

Hello QV Community,

I'm having an issue figuring out the syntax for the code below. I'm basically trying to pull in all of the rows from a SQL table and add them to a QVD. To do so, I'm loading the QVD and SQL tables, then trying to create a third temp table that loads the results from the SQL table where the Key doesn't exist in the first table.

Is there a simple way to do a 'not in' function in a load or nest a load statement with a select statement? Or a much simpler way to do it than I'm trying to do? Thanks for your help!!

Temp1:

LOAD KeyX

FROM c:\Key.QVD (qvd) ;

Temp2:

SELECT

KeyX

,Field1

,Field2

,Field3

FROM [DW].DBO.Report;

Temp3:

LOAD

KeyX

,Field1

,Field2

,Field3

RESIDENT Temp2

WHERE KeyX not in (LOAD * RESIDENT Temp1)

Best,

Andy

Re: QlikView 'Not In' exclude functionality

tresB — Tue, 24 Sep 2013 16:57:51 GMT

Have a look into exists(), match() functions.

For your case, try like:

WHERE not Exists(Temp1.KeyX, KeyX)

Re: QlikView 'Not In' exclude functionality

MayilVahanan — Tue, 24 Sep 2013 16:58:20 GMT

Try like this

Where not exists(Keyx,Keyx)

Re: QlikView 'Not In' exclude functionality

Tue, 24 Sep 2013 17:02:40 GMT

Hey Mate,

Check this post, some very helpful information

http://community.qlik.com/thread/93664

To get your script to reduce the results based on your criteria, apply the following

Temp1:

LOAD KeyX

FROM c:\Key.QVD (qvd) ;

Temp2: //apply preceding load

LOAD *

WHERE not exists (KeyX,KeyX2); //Will load all values from SQL table where KeyX2 does not appear in KeyX

SELECT

KeyX as KeyX2 //Rename this field as indicated

,Field1

,Field2

,Field3

FROM [DW].DBO.Report;