"Not In" in Qlikview Same as SQL

[#1]

Hello Friends,  I want to do "not in" same as SQL in qlikview as follow.   LOAD * FROM $(v_1) (qvd) WHERE NUMBER NOT IN  (    LOAD NUMBER    FROM $(v_1) (qvd) );   Can any one please help me, how to write above statement?

[marcus_sommer]

You could use mapping (F1) for this:

MapTable:

Mapping

LOAD NUMBER, rowno() as DS FROM $(v_1) (qvd);

Tabel1:

LOAD * FROM $(v_1) (qvd) Where applymap('MapTable', NUMBER, 'x') = 'x';

- Marcus

[swuehl]

In general, I would use the match function to emulate 'not in (1,2,3,4)'

LOAD * FROM DB.qvd (qvd)

WHERE NOT MATCH(NUMBER, 1, 2, 3, 4);

In your special case using a subquery, you can also consider using EXISTS() :

LOAD NUMBER as EXCLUDE FROM DB1.qvd (qvd);

LOAD * FROM DB2.qvd (qvd)

WHERE NOT EXISTS(EXCLUDE, NUMBER);