topic "Not In" in Qlikview Same as SQL in QlikView

"Not In" in Qlikview Same as SQL

Wed, 24 Jul 2013 13:08:05 GMT

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?

Re: "Not In" in Qlikview Same as SQL

marcus_sommer — Wed, 24 Jul 2013 13:18:45 GMT

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

Re: "Not In" in Qlikview Same as SQL

swuehl — Wed, 24 Jul 2013 13:41:54 GMT

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