Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Select from set A where not in Set B

How can I write the following (or create a flag) as an expression in a straight table. I cannot write that in load statement.

select field1, field2 from tableA field3 not in (select field3 from tableB where field4 in ('x','y','z'));

1 Solution

Accepted Solutions
PrashantSangle

Hi,

use not exist()

1st load tableB

then load tableA with where clause

try like

TableB:

Load a,

b

from TableB;

TableA:

Load a,

c

from TableA

where not exists(a,a)

Note: for detail working of exists() check QV help.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Cud you share some sample data??

PrashantSangle

Hi,

use not exist()

1st load tableB

then load tableA with where clause

try like

TableB:

Load a,

b

from TableB;

TableA:

Load a,

c

from TableA

where not exists(a,a)

Note: for detail working of exists() check QV help.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
petter
Partner - Champion III
Partner - Champion III

You can get your logic to work both in a Load Script and also in the front-end.

In the load script you could use Not( Exists( 'CompositeKeyField' , CompositeKeyValue ) ) in a WHERE clause of a LOAD statement.

Field In ('a','b','c') can be written as Pick( Match(  Field , 'a','b','c' ) ) in QlikView.

In the front-end you could query it by at least two approaches:

1) Just by using the associative logic of QlikView - under the assumption that you have tableB loaded an associated correctly with the fields you want results from. Then you do the "Select excluded" option on a List Box after having selected the values.

2) By using Set Expression in your expression(s) in a chart.