Selecting with groups / using OR statements

[#1]

I'm wondering about writing a macro that can make selections with groups of criteria separated by some kind of OR statements. In broken SQL-ish, this is basically what I'm looking for:

SELECT WHERE (

field_a = 'test' AND field_b = 'test2'

)

OR (

field_b = 'test3' AND field_c = 'test4'

)

One key is that both groups need to be able to refer to different values for the same field. Apologies if this is a common problem, I haven't been able to find anything by searching.

Thanks very much for any help.

[#2]

The only thing I've been able to learn so far is that, at least for 8.5, using OR's in Qlikview is pretty "limited". Anybody have any input on this? Seems like it must be a really common problem. Thanks.

[#3]

You could try reading each statement in separately and then if the records are mutually exclusive you could concatenate the second set into the first set.

Alternatively, you could read the first set and second set individually, and then try to join the tables together. Perhaps that will work for you.

[#4]

Hi mhamberg, thanks for the reply. I think I understand what you're getting at but I don't follow how I could do that in a macro, the key is that I need to be able to apply the selection on top of any pre-existing selection. Thanks.

[#5 · Accepted solution]

Sorry to bring up such an old thread, but I wanted to post the solution for this in case it’s helpful to anybody else who stumbles upon this.

The key detail I forgot to specify is that our data is centered around constituents so the constituent ID field (Corebio ID) links pretty much all of our tables (would one call that a foreign key?). What I wanted to do was be able to select constituents by criteria that was separated by OR statements. For example: Give me everybody who either graduated in 1998 OR lives in England. Or maybe: give me everybody who either graduated in 1998 and lives in Texas, OR graduated in the 1970’s and lives in England.

What I did was pretty simple, I just made a document variable called vCustomSelection and wrote a macro (CS_addWids) that adds a comma separated list of the currently selected ID’s to it. Then I made another macro (CS_select) that makes a selection based on the IDs in the variable. So you can make any selection you want, add the ID’s to the variable with CS_addWids, then start over and make any other selection, add those IDs to the variable, etc. When you’re done, you can just use CS_select to make a selection based on the list of IDs you’ve made in vCustomSelection. Here’s the code for the macros:

'Add wids to the custom selection variable (wid = custituent ID)

sub CS_addWids

   set var_cs       = ActiveDocument.Variables("vCustomSelection")

   set var_wids        = ActiveDocument.Fields("Corebio ID").GetPossibleValues("10000")

   dim temp

   'if there are already wids in there, add a comma

   if len(var_cs.GetContent.String) > 0 then

       temp = var_cs.GetContent.String & ","

       var_cs.SetContent temp, true

   end if

   'add a single quote for the first wid

   temp = var_cs.GetContent.String & "'"

   var_cs.SetContent temp, true

   'Loop through all wids in the var_wids array and add them to the temp string

   for i = 0 to var_wids.count - 1

       if i = var_wids.count - 1 then

           temp = temp & var_wids.Item(i).text & "'"

       else

           temp = temp & var_wids.Item(i).text & "','"

       end if

   Next

   'Set vCustomSelection = temp

   var_cs.SetContent temp, true

end sub

'Make a selection with the custom selection variable

sub CS_select

   set var_field        = ActiveDocument.Fields("Corebio ID")

   set var_cs       = ActiveDocument.Variables("vCustomSelection")

   ActiveDocument.ClearAll 'clear all selections first

   var_field.Select "=match([Corebio ID],"&var_cs.getContent.String&")"

end sub

It’s pretty crude and I’m sure it’s not going to be quick at all for large numbers of records, but for our needs I was able to limit it to 10k records and it does alright.

It’s not rocket science, but for somebody who doesn’t know VB figuring out the syntax was brutal, so I figured I’d post it here in case I can save somebody else the trouble. I’m guessing there are better ways to do this with later versions of Qlikview, but for those of us stuck with 8.5 this seems like it might be our only option, though I certainly welcome being proven wrong about that.