Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikViews equivalent to NOT IN?

Hello,

I am trying to create a not in query of a resident table. In SQL the statement would be something like:

SELECT * FROM (SELECT * from table_1 where x = 'y' and z = 'x') t

WHERE id NOT IN (SELECT id from table_1 as t2 where t2.x = 'yy' and z = 'xx')

Can anyone help me?

Thanks,

James Ford

13 Replies
Not applicable
Author

So I guess it's not possible to do this in qlikview? The equivalent SQL-query is so basic. I don't get this?

whiteline
Master II
Master II

I think you can use AND mode of listboxes.

And a secred 'red' color of selection that means 'not in'.

flipside
Partner - Specialist II
Partner - Specialist II

Hi,

I've been stumped by this simple problem but think I may have a solution.  Due to Qlikview's associative logic, you would need to first create a disassociated table to enable selection of product codes excluded by the first selection.

Data:
Load * Inline [
ID,  Product, Active
1,  A,   Y
1,  B,   N
2,  C,   Y
3,  A,   Y
3,  B,   Y
3,  B,   N
4,  C,   N
10,  A,   Y
3,  A,   N];


Inactives: //Creates a data island to allow selection of same products
LOAD 'No exclusions' as ProductX,
0 as IDX,
chr(39) & 0 & chr(39) as lkpIDX
AutoGenerate 1;
LOAD Distinct
Product as ProductX,
ID as IDX,
chr(39) & ID & chr(39) as lkpIDX
Resident Data
Where Active = 'N';

In this table I included fields wrapped with the ' char to simplify a concat statement in a variable.  You then need to set up a variable with the following statement ...

=If(IsNull(GetFieldSelections(ProductX)),'0',Concat(distinct lkpIDX,',',IDX))

... which creates a string to match to if there are selections in the excluded products.

After you have two list boxes - one for Product and one for ProductX (excluded) - your final listbox or chart needs this expression  ...

=if(Match(ID,$(variable)),Null(),ID)

This is easier in a SQL front-end (such as SSRS) because you re-submit the query with the required parameters to return the data, whereas in Qlikview your data is all there and you are filtering it down.

Hope this helps

flipside

sofiene_1920
Partner - Contributor III
Partner - Contributor III

Thank you.