topic Exclude many values in where statement? in QlikView

Exclude many values in where statement?

mr_novice — Tue, 20 Mar 2012 15:23:31 GMT

Hello all!

Is there a good way of excluding several values in a 'where' statement?

I would like to do something like this:

Table1:
Load *
from MyTable where NOT FieldName='x','y','z';

That is, I want to exclude the values x,y,z in my load.

cristian

Exclude many values in where statement?

michael_maeuser — Tue, 20 Mar 2012 15:46:13 GMT

Hi, there are two ways i guess: 1. from MyTable where NOT FieldName='x' and FieldName='y' and FieldName='z';

or 2. from MyTable where FieldName<>'x' and FieldName<>'y' and FieldName<>'z';

Exclude many values in where statement?

mr_novice — Tue, 20 Mar 2012 15:47:56 GMT

Ok, so there's no way to avoid repeating the fieldname? I mean I have like ten values to exclude so that would men repeating the field name ten times.

cristian

Exclude many values in where statement?

Tue, 20 Mar 2012 15:50:41 GMT

Not sure if it will work, but try this:

from MyTable where NOT FieldName in ('x','y','z');

Exclude many values in where statement?

mr_novice — Tue, 20 Mar 2012 15:57:45 GMT

Hello,

Tahnk you but it doenst seem to work.

cristian

Exclude many values in where statement?

erichshiino — Tue, 20 Mar 2012 18:43:05 GMT

Hi, you can create a table with the values you want to exclude, then you use a where not exists command:.

For example:

Exclusion:

LOAD * INLINE [

Exclude

];

Table:

LOAD * INLINE [

FieldName

]

where not Exists( Exclude, FieldName)

;

Hope this helps,

Erich