topic Selecting all but one values via Macro in QlikView

Selecting all but one values via Macro

ramonarcusa36 — Wed, 04 Jul 2018 08:55:29 GMT

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!

Re: Selecting all but one values via Macro

BalaBhaskar_Qlik — Wed, 04 Jul 2018 13:18:06 GMT

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

Re: Selecting all but one values via Macro

marcus_sommer — Wed, 04 Jul 2018 14:15:25 GMT

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

Re: Selecting all but one values via Macro

marcus_sommer — Wed, 04 Jul 2018 14:32:20 GMT

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

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

- Marcus

Re: Selecting all but one values via Macro

ramonarcusa36 — Sat, 07 Jul 2018 12:18:20 GMT

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

Thanks!

Re: Selecting all but one values via Macro

Gatter — Fri, 05 Jun 2020 11:12:47 GMT

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