Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: QlikViews equivalent to NOT IN?

Have you tried the exists function?

Exists ()

Not applicable

Re: QlikViews equivalent to NOT IN?

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

MVP
MVP

Re: QlikViews equivalent to NOT IN?

Hello James,

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

Regards,

tresesco

Not applicable

Re: QlikViews equivalent to NOT IN?

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

Lets say we have the following table:

qlikviewsuger.PNG

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.

Not applicable

Re: QlikViews equivalent to NOT IN?

Bump

Not applicable

Re: QlikViews equivalent to NOT IN?

Bump

Not applicable

Re: QlikViews equivalent to NOT IN?

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.

dakohler
New Contributor III

Re: QlikViews equivalent to NOT IN?

hi,

hope this helps.

regards,

Not applicable

Re: QlikViews equivalent to NOT IN?

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.

Community Browser