QlikView 'Not In' exclude functionality

[andyquirin]

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

[tresesco]

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

For your case, try like:

WHERE not  Exists(Temp1.KeyX, KeyX)

[MayilVahanan]

HI

Try like this

Where not exists(Keyx,Keyx)

[#4 · Accepted solution]

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;