Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ramonarcusa36
New Contributor III

Selecting all but one values via Macro

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!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Selecting all but one values via Macro

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

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

- Marcus

View solution in original post

4 Replies
balabhaskarqlik
Honored Contributor

Re: Selecting all but one values via Macro

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

MVP & Luminary
MVP & Luminary

Re: Selecting all but one values via Macro

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

MVP & Luminary
MVP & Luminary

Re: Selecting all but one values via Macro

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

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

- Marcus

View solution in original post

Highlighted
ramonarcusa36
New Contributor III

Re: Selecting all but one values via Macro

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

Thanks!