topic Select records without a specific matched record (one-to-many join) in QlikView

Select records without a specific matched record (one-to-many join)

Wed, 07 Apr 2010 15:33:54 GMT

Hello

I've got a one-to-many join in my QlikView application as follows (matched by ID):

Master file	Detail file

ID	ID	Seq	Flag
A	A	1	X
B	A	2	Y
C	B	1	X
D	C	1	Z
	C	2	Z
	D	1	Y

If I create a listbox based on the Flag field then I can select all ID's that have a particular flag value on any of their matching detail records. For example, if I pick flag X, QV will show me IDs A and B.

However what I want is to select ID's that don't have particular flag on any of their matching detail records.

For example, if I select flag X I want QV to only show IDs C and D (the only two IDs that don't have flag X on any of their matching detail records). If I select flag Y I want QV to only show IDs B and C (the only two that don't have flag Y on any of their matching detail records).

I've attached an application which hopefully clarifies further. Hope this makes sense! Any ideas?

Thanks

James

Select records without a specific matched record (one-to-many join)

Wed, 07 Apr 2010 16:40:57 GMT

Hello James

First, you should understand that QlikView does not allow reverse selections (i.e. everything except for XXXX), but there are a couple of ways at least to achieve this.

I put an example up here that uses a stand alone variable which holds the equivalent of each of your FLAG values, this can be replaced by adding something to your load script to select flags as different names (I added some commented code to your load script to give you an idea). The SelectFlag variable is then provided as a list box to select from, the variable has a macro called whenever it is changed and the macro simply select everything on FLAG except for the value selected on SelectFlag. Hope I'm making some sense here.

This is one way to achieve it, I'll put another up in a little while.

Select records without a specific matched record (one-to-many join)

Wed, 07 Apr 2010 16:57:44 GMT

James

My other idea was to use set analysis to achieve it, but I can't make it work at the moment and must get on with a bit of work!!

Hopefully somebody will come up with a Set Analysis solution for you, I think it might be easier to implement.

Select records without a specific matched record (one-to-many join)

Wed, 07 Apr 2010 17:33:04 GMT

Thanks so far Nigel!

I looked at the example, but the new listbox you created seems to perform a "select excluded". So when I select flag="X" I still get ID "A" appearing, which I don't want because one of its matched detail records contains an "X" flag.

Am I understanding it right?

Select records without a specific matched record (one-to-many join)

Wed, 07 Apr 2010 17:59:24 GMT

Hi James

You're absolutely right, I missed that little bit of detail !! Sorry about that, I'll take another look and see what I can come up with, it must be possible to achieve it.

Select records without a specific matched record (one-to-many join)

johnw — Thu, 08 Apr 2010 00:11:03 GMT

How about this? Trigger it onSelect or onChange of Flag.

sub selectIDs
ActiveDocument.Fields("ID").Clear
ActiveDocument.Fields("ID").SelectPossible
ActiveDocument.Fields("ID").SelectExcluded
end sub

Not sure if it's exactly what you want overall, but it DOES seem to select the right values for the ID. And the "selected" value of the Flag ends up highlighted in gray, so there's still an indication of some sort that it's what you selected. Could probably do it with actions in version 9.

Edit: Hmmm, it doesn't work if you select every possible value of the flag, because then the select excluded can't select anything, so it selects nothing, which means the macro ends up doing nothing. Not sure if that's a problem in practice.

Select records without a specific matched record (one-to-many join)

Thu, 08 Apr 2010 10:16:17 GMT

That works great, selecting all the values is not really a problem,

Thanks for your help!