Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andyquirin
New Contributor III

QlikView 'Not In' exclude functionality

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

1 Solution

Accepted Solutions
Not applicable

Re: QlikView 'Not In' exclude functionality

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;

3 Replies
MVP
MVP

Re: QlikView 'Not In' exclude functionality

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

For your case, try like:

WHERE not  Exists(Temp1.KeyX, KeyX)

Re: QlikView 'Not In' exclude functionality

HI

Try like this

Where not exists(Keyx,Keyx)

Not applicable

Re: QlikView 'Not In' exclude functionality

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;

Community Browser