Select from set A where not in Set B

[#1]

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'));

[Anonymous]

Cud you share some sample data??

[PrashantSangle · Accepted solution]

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

[petter]

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.