Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andyquirin
Partner
Partner

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

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;

View solution in original post

3 Replies
tresesco
MVP
MVP

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)

Thanks & Regards,
Mayil Vahanan R
Not applicable

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;

View solution in original post