topic Re: Negative Search in QlikView

Negative Search

NareshGuntur — Fri, 27 May 2016 13:02:31 GMT

Hi,

I am trying to do a negative search.

In a normal list box, I have values like Japan, Singapore, UK & USA. If I search for Japan, Japan will be shown. I wanted to select everything except Japan. so is there any negative search that I can use??

I have tried some symbols but failed.

I know that I can search for Japan, select it and then "Select Excluded".

But I wanted that in one step.

Cheers,

Naresh

hic‌jagan‌gwassenaar‌stalwar1‌

Re: Negative Search

sunny_talwar — Fri, 27 May 2016 13:04:58 GMT

I think swuehl‌ is the person who can answer this in the best way.

Take a look at his document here -> Compound Search - demystified

Re: Negative Search

swuehl — Fri, 27 May 2016 13:07:12 GMT

Try a compound search like

(* ^ Japan)

Re: Negative Search

alexandros17 — Fri, 27 May 2016 13:13:48 GMT

Try With

"(<Japan|>Japan)"

Re: Negative Search

swuehl — Fri, 27 May 2016 13:18:25 GMT

Or maybe

=Country <> 'Japan'

=Only({1}Country) <> 'Japan'

if you need to ignore the current selection

Re: Negative Search

sunny_talwar — Fri, 27 May 2016 13:23:14 GMT

I am just curious Alessandro‌ and Stefan‌ does Qlik actually expect users to enter these complicated search criteria to look for results. I guess select in field actions make sense, but I would have though that a simple -Japan or <>Japan should work. What do you guys think?

Re: Negative Search

swuehl — Fri, 27 May 2016 13:25:15 GMT

Does this work at your site? Do you have a preview version installed?

Re: Negative Search

stevelord — Fri, 27 May 2016 13:27:17 GMT

Edit2: Think I misread your initial need, but if you consistently need to exclude Japan from your results. Feel free to use below. Looks like you have a good army of people giving other ideas for the negative search itself.

Hi, if you have a table and want to list the stuff not in Japan, you can make a straight table like:

Dimensions: Country and whatever else

Expression: Sum( if(Country<>'Japan', Sales))

I actually have a 'not registered' list to count people not registered for events. So when user selects event dates, it shows who is not registered for those events.

Dimensions: UserId, fname, lname

Expression: count(distinct if(len(EventDate)=0, UserId)) (or something like that)

And I can easily confirm it works by comparing to a regular table box of userid fname lname if I have names sorted alphabetically. You can watch people move from one list to the other as you make different selections.

You will want to note something clearly in the caption or maybe a nearby textbox, that this is showing who's NOT in a particular selection. Since it is working the opposite of the way of the rest of the tables.

Also, if you need to keep other countries in even if they have 0, you may add a set analysis to the end like

+Sum({<Country, SalesDate>} if(Country<>'Japan', 0))

(I think, I did that on the fly in this post. I have lists where I want to keep users on the list even if they did nothing, and let it populate a 0 or 1 depending on the column and what I'm counting.)

Enjoy!

Edit: Do feel free to try that on bar graphs and such too. I just like to start with tables and lists so I can see things working properly, then compare finalized graph #s to the lists I have, then drop the lists if they're not needed.

Re: Negative Search

sunny_talwar — Fri, 27 May 2016 13:27:21 GMT

1) Does this work? No

2) Preview version installed? What is that?

Re: Negative Search

NareshGuntur — Fri, 27 May 2016 13:27:52 GMT

Thanks Swuehl.

Its cool.

Cheers,

Naresh

Re: Negative Search

swuehl — Fri, 27 May 2016 13:31:15 GMT

You're welcome.

Do you really need the last star symbol? Do you need to exclude values like 'Japan' and 'Japanese'?

Re: Negative Search

swuehl — Fri, 27 May 2016 13:35:14 GMT

2) Misunderstood your comment. Thought you have seen this working

I agree that these kind of searches should be easier, I was hoping you had access to a new beta version...

Re: Negative Search

NareshGuntur — Fri, 27 May 2016 13:37:04 GMT

You are correct again

* at the end is not required.

Modified my response.

Cheers,

Naresh

Re: Negative Search

sunny_talwar — Fri, 27 May 2016 13:38:05 GMT

I wish I did .

For search (actually for any QlikView question that I have or might have), you are my go to man.

Re: Negative Search

santiago_respane — Fri, 27 May 2016 13:38:21 GMT

Swuehl this is excelent!

Using your solution i solved it doing as follows:

-Lets suppose i want to exclude values when i select on field X

-i created a copy of the field named excludedX

-Added a field trigger for excludedX that when i select a value in it, it selects the field X using your wildcard like this:

='(*^' & ONLY(excludedX) & ')'

do you think this is a good solution?

Kind regards,

Re: Negative Search

kamal_sanguri — Mon, 30 May 2016 07:30:50 GMT

There is one more cool way to do that.. Just search and select the value you want to exclude and add a trigger to select excluded values..

Attaching a QVW to review..

-Kamal

Re: Negative Search

NareshGuntur — Wed, 15 Jun 2016 07:58:05 GMT

Just noticed

-Japan(hyphen Japan) is also working the same way.

Cheers,

Naresh