How can I search in a filter for multiple values to exclude? Basically the opposite of (1|2|3|4|5)

[dgreenberg]

I want to filter a field where the values are not in (1,2,3,4,5).

If I wanted the opposite that's simple it's (1|2|3|4|5).

I do not want to do this with triggers it would truly be best to do it as a filter saved in a bookmark for various reasons.

The list of values will change so I can't just select excluded and save it as a bookmark.

If this cannot be done I will set a flag in the data model something like:

Load RestrictionID,if(wildmatch(restrictionID,1,2,3,4,5)<>0,1,0 as RestrictioNFlag

Resident RestrictionTable;

That's not a bad solution but I feel like this might be able to be accomplished as a filter selection in a bookmark without triggers.

Here is where it gets interesting:

(-1|7|8|9|<>11|14|31|35)

That selects -1,7,8,9 excludes 11, includes 14,31,35

Great so you would think I could use the <> anywhere in the list but you can't it ignores it after the first occurrence.

So:

(-1|7|<>8|9|<>11|14|31|35)

Will only exclude 8 and will include 11.  Weird isn't it?

Since it does accept the <> in 1 position I am hopeful there might be some crazy syntax to make this work.

I do know of many other ways to achieve the same results with triggers and flags in the data model but really want to keep it simple with a field selection saved in a bookmark.

Thanks

Dan

[sunny_talwar]

Okay seems like Stefan's solution works for you... but I have no idea why you think this wouldn't work. swuehl‌ do you think that this won't work?

[dgreenberg]

Sunny thanks for the effort but that is something that I tried first before posting.

You are both right and wrong in that it will work, it depends.

Sometimes the current selections will say Not other times it will specifically pick the ones you want.

If I just do (-1|7) and then selected excluded I get this in current selections:

NOT -1, 7  //so in this case you are 100% correct and it would work

However if I do my actual selection (-1|7|8|9|11|14|31|35) on a list that contains 30 values and then select excluded it says 22 of 30.  If I expand that list by use of

=GetCurrentSelections(chr(10),': ',', ',1000)

it is actually enumerating the values as shown in the image below.

So in this case your solution will not work because if 45,46,47 get added to the list they will not be included.

I am sorry if it was a waste of time because I wasn't specific enough but we all know that sometimes a limited POC doesn't function the same as when we have more data.

I was really hoping it would show NOT -1,7,8,9,11,14,31,35 as the current selection but that was no the case.

[sunny_talwar]

So, this was not more about getting the right set of selection, but seeing something specific for current selection . There might be a way to fix that in current selection, but no point spending time on this now since we already have a solution. I am glad you were able to get what you wanted using Stefan's solution.

Best,

Sunny

[dgreenberg]

No not at all I don't care what the current select box says but correct me if I am wrong and if I am I owe you a pint too.

If the current selection box when expanded shows a list 0,2,3,4,15,16,17,19,24,25,26,27,28,29,32,33,34,36,29,40,41,30 and I save that as a Bookmark would it not then apply those selections any time the bookmark is applied?

So tomorrow 5 new ID's are added that were not there today and I apply the bookmark I created today which was created before those 5 new ID's were added.

How would QlikView possibly know to include those 5 new ones?  It's not like tomorrow I am re-applying the filter, selecting excluded.  I am applying a filter which specifically says 0,2,3,4,15,16,17,19,24,25,26,27,28,29,32,33,34,36,29,40,41,30

If it were just about the display in the current selections I wouldn't care as I always add help

=GetCurrentSelections(chr(10),': ',', ',1000)

So if you click on the help ? it will show the expanded list I pasted in.

[sunny_talwar]

I might be wrong (and swuehl‌ can correct me), but I feel like selection wise the two expression should give you the exact same output. I am not sure how you are applying the bookmark in Stefan's case, but whichever way you are applying that, the same way should work for what I am suggesting.

Having said that, I think Stefan's expression is more straight forward, so I wouldn't blame you if you wouldn't want to try this method (cause I would have used his method myself ), but I feel that the above technique should work the same way.

[dgreenberg]

Not trying to beat the dead horse here but if it enumerates the selected values instead of saying NOT -1,7.... how could that possibly work if the list of values changes over time.

Keeping it simple and maybe so simple that it will say NOT 1,2

If there are 30 values 1-30 and I select 1,2,3,4,5,6,7,8,9,10 and then select excluded

if it shows it as 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

How in the world would that bookmark ever include 31,32,33,34,35 if they are added at a later date.

The only way this could work is if QlikView is displaying one thing when you enumerate the current selections and actually doing another thing behind the scenes but I really don't think so.

When I have time I'll dummy a test so we know for sure.

My test will have the same values in this field as it does today.  I will select (-1|7|8|9|11|14|31|35) then select excluded, then I will save it as a bookmark.

Then I will add 5 more ID's to the field and reload and apply the bookmark.

Then we will know for certain.

Look for those results later today.

[sunny_talwar]

Once again, I am not sure how is bookmark coming into the picture. But if you can create a sample where Stefan's solution is working for you, I will be check/show how the above technique will work for you. If you don't have time to put it together, doesn't matter, but I did post two samples above, if that makes you are life easy, feel free to add Stefan's solution and your approach and re-post it

[dgreenberg]

The attachment has 2 bookmarks.

One with the select then select excluded method

the other with (*^(-1|7|8|9|11|14|31|35))

if you follow the instructions in the script first running the first load script commenting out the 2nd it loads

The current set of values.

I selected (-1|7|8|9|11|14|31|35) then selected exlcuded and saved it as a bookmark.

Then I commented out the first load script and uncommented the 2nd one so it did the same load but added 5 more values.

If I then apply the bookmark that was created before 42,43,44,45 were added those values will not be included.

However If I created a bookmark with (*^(-1|7|8|9|11|14|31|35)) that will work regardless of how many values are added in the future.

So like I have been saying if the bookmark is created and what is displayed is not

NOT -1,7,8,9,11,14,31,35 then this simply will not work if more values are added.

I don't care what the display shows in current selections but when it enumerates values its going to select those explicit values any time the bookmark is applied.

[sunny_talwar]

First of all thanks for taking the time for create this sample. I see exactly what you mean here and I understand your concerns now. Because, when you created the bookmark, it only showed the ids that were present at the time of the bookmark, it wouldn't be able to include anything added later. So, it does make sense why the solution I provided doesn't work for you  (so no beer owed to me now )

[dgreenberg]

Sunny again thanks for the effort and like I said before sometimes it would work but not if it's a longer list to exclude and of possible values.

Thanks again!