Select records without a specific matched record (one-to-many join)
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?
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.
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.
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.
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.
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.
