topic Re: Select from set A where not in Set B in QlikView

Select from set A where not in Set B

Mon, 12 Oct 2015 03:53:35 GMT

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

Re: Select from set A where not in Set B

Anonymous — Mon, 12 Oct 2015 08:19:12 GMT

Cud you share some sample data??

Re: Select from set A where not in Set B

PrashantSangle — Mon, 12 Oct 2015 08:26:15 GMT

Hi,

use not exist()

1st load tableB

then load tableA with where clause

try like

TableB:

Load a,

from TableB;

TableA:

Load a,

from TableA

where not exists(a,a)

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

Regards

Re: Select from set A where not in Set B

petter — Mon, 12 Oct 2015 08:39:57 GMT

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.