Selecting all but one values via Macro

[ramonarcusa36]

Hey guys.

Let's suppose I need to select all my countries but France (no macros, just a regular selection). I can get it just by typing this in the search bar: -FRANCE. Goal achieved!

Now, let's try to do it via macro. I know I could do this:

ActiveDocument.Fields("Country").Select "FRANCE"

ActiveDocument.Fields("Country").SelectedExcluded

But this method has a problem: I may already have other selections (in other fields) and the new selection Country = France could be not matching them (for example, let's imagine I had selected City = Rome). If that happens, QlikView will remove as many selections as it needs to show data for France (normal QlikView behavior, as we know). I would lose the City = Rome filter, only because I wanted to exclude France. Locking all those previous selections wouldn't be a good choice here.

I thought I could try to work around this just by trying the minus sign approach. That is, selecting "-FRANCE" instead of having to take the two steps previously explained. But when I try to do this:

ActiveDocument.Fields("Country").Select "-FRANCE"

It just doesn't work.

Please, is there any way to achieve this?

Thanks in advance!

[balabhaskarqlik]

Maybe this,

you can use the toggleselect function.

Here is an example.

sub NameSub

set FIELDNAME = activedocument.fields("FIELDNAME")

FIELDNAME.select "20200" ' clears all in FIELDNAME and selects 20200

FIELDNAME.toggleselect "50300" ' selects 50300

FIELDNAME.toggleselect "60200" ' selects 60200

end sub

[marcus_sommer]

I'm not sure if it's possible - if it it will be probably a matter of quotes-syntax like doubling them or something similar stuff. Unfortunately it's not visible how the macro called this selection respectively how it will be interpreted from the field. This means it would be an ugly trial and error ...

As an alternatively you could use something like this:

ActiveDocument.Fields("Country").Select "=match(Country, 'FRANCE')=0"

- Marcus

[marcus_sommer · Accepted solution]

Regarding to the excellent posting from Stefan here: Compound Search - demystified you could also use:

ActiveDocument.Fields("Country").Select "(* ^ *FRANCE*)"

- Marcus

[ramonarcusa36]

That's perfect. That helped me a lot. I solved my problem.

Thanks!

[Gatter]

Hello Marcus,

I have searched many things and this was one of the first to work, so thank you for this.  Although I still have one issue as my situation is a bit different that just the example below.  What can I do if I need to "select all" from a field, but exclude more than 1 of what is in that filed?

In like the example below say all European countries were listed and in this statement both France, Italy and England needed to be excluded?

Thanks