QlikViews equivalent to NOT IN?

[#1]

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

[#2]

Have you tried the exists function?

Exists ()

[#3]

Can you give me an example? I am also wondering if there is any syntax specifikation for qlikview?

[tresesco]

Hello James,

Have a look at the attached appication (courtsey: some community user), might help.

Regards,

tresesco

[#5]

Hello again, let me show you what I want to achieve.

Lets say we have the following table:

First I would like to select the customers that is active on Product A:

SELECT * FROM Customers WHERE Product = 'A' and Active = 'Y'

This will give us customers with id 1 and 3.

And based on that selection I would like to exclude those that are NOT active on product B. This would give us customer 3.

So there is only one table involed. Based on the first selection I would like to create a NOT IN query on the same table.

[#6]

Bump

[#7]

Bump

[#8]

Hi James,

Try to use this where statement:

WHERE not wildmatch(FieldName, 'value', 'value2', 'value3')

value, value2, value3 should be data you want to exclude.

You can either use wildmatch or mixmatch function.

wildmatch --> 'valu*'  (wildmatch search)

mixmatch --> 'value' (exact data value search)

Hope it helps.

[Anonymous]

hi,

hope this helps.

regards,

[#10]

Hello,

Thanks for the answer. But that is not what I want to achieve.

I want to use two listboxes to make my selection. The first one would be the 'Active On Product' selection. And the second box would be based on the first selection of the 'Active On Product' listbox.  And would be 'Not active on' where you select one or more products that the customers are NOT active on based on the first selection.