Negative Search

[NareshGuntur]

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‌

[sunny_talwar]

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

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

[swuehl · Accepted solution]

Try a compound search like

(* ^ Japan)

See also

Compound Search - demystified

[alexandros17]

Try With

"(<Japan|>Japan)"

[swuehl]

Or maybe

=Country <> 'Japan'

or

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

if you need to ignore the current selection

[sunny_talwar]

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?

[swuehl]

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

[stevelord]

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.

[sunny_talwar]

1) Does this work? No

2) Preview version installed? What is that?

[NareshGuntur]

Thanks Swuehl.

Its cool.

Cheers,

Naresh