How Do I Filter for the Non-Existence of Child Records?

[#1]

Let's say you have a file based on two tables, Orders and OrderDetails.  A relationship exists between them, and QlikView joins the tables automatically when necessary (and occasionally when NOT necessary, but that's another topic).  OrderDetails contains fields such as, say, "Color."  If I'd like to get a list of orders which have one or more order details of a particular color, that's easy: create a list box for Color and select my favorite hue.

How do I select orders where there is no existing child record of a particular type?  If I want all orders which do not have any order details which are green, or have flanges, or are priced over $100, how do I go about making such a selection?  In other words, an anti-join.

If I select the category I don't want (e.g., green), and then choose "Select excluded," what I get is all orders which contain one or more details which are not green.  This is close, but it includes orders which have both green and non-green details.

I realize there are various ways to do this while loading data (EXISTS), but I'm trying to help a non-technical user do something without building a new file.

The best workaround I've come up with so far is to add a list box with an expression based on the presence of absence of the type of detail excluded:

=If(
Sum(If([Color] = 'Green', 1, 0)) > 0,
'Has one or more green details',
'Does not have any green details'
)

However, I'm not sure how to use this expression to filter.  Put in a list box, and nothing shows.  Put in a straight table as a dimension it gives an amazingly vague "Error in calculated dimension" error.  Added as an expression in a straght table, it calculates, but clicking the desired value ("Does not have...") just filters for that single record, not all orders without green details.

Surely this is a very common use case, and I've just failed to find it while searching the forum.  I would appreciate any direction.  Thank you!

[swuehl]

Jonathan,

you usually need to give a dimension to make something like this to work.

You were saying, the expression worked in a straight table, right?

So just take the dimension of that straight table and use as a list box field expression:

=aggr(

If(Sum(If([Color] >'Green', 1, 0)) > 0,

'Has one or more green details',

'Does not have any green details')

,DIMENSIONFIELD)

Hope this helps,

Stefan

[#3]

Huh.  That's... odd.  Is this really the most intuitive way to handle anti-joins?  I'm quite sure I'll have to help every user with it the first half-dozen times they need it, but it does work.

[swuehl · Accepted solution]

If I select the category I don't want (e.g., green), and then choose "Select excluded," what I get is all orders which contain one or more details which are not green.  This is close, but it includes orders which have both green and non-green details.

If you "Select excluded", you did this from the category list box? This will of course select all other colors and then you get all orders linked which contain one or more details which are not green as possible values.

But try to "Select excluded" from the Orders list box, after selecting the category, so inverting the possible selection state of Orders.

[#5]

The "select excluded IDs" trick works, and on balance it's probably more intuitive for most users.  I like the Aggr() method too, but most users will be scared off by the elaborate expression.

Thank you!

[swuehl]

Yes, I agree using the standard functions provided in the context menu is much more convenient and should be intuitive once we understood how QV works (everything linked / the 3 colors thing...).

The user actually see the result of the select excluded operation before he performs the action, just needs to look for the grey values (unlike using the list box with an expression).

Thus I believe he will get more and more into understanding QV rather than just consuming predefined list boxes (which might still be preferable in some cases, for e.g. defined enterprise measures). For those cases, you could also consider creating a flag in the script to indicate if an order belongs to one or the other group.

Regards,

Stefan