Button doing a NOT value selection in field?

[anderseriksson]

Say I have a field 'Category' with possible values 'A', 'B', 'C'.

Now I want a button to make a selection in field 'Category' and i want the selection to be 'NOT B'.

'NOT B' is not the same thing as selecting 'A' & 'C' as there can also be rows in the datamodel without any value in field 'Category'.

Those rows are included with the selection 'NOT B' but excluded if you do selection 'A' & 'C'.

The only way of accomplishing 'NOT B' in a button trigger that I know of is by first doing a field selection 'B' and then select excluded values.

Problem is this will not do when user has made other selections.

Say I also have a field 'Year' and user made the selection '2013' in this field.

If there are no 'B's during 2013 what will happen with the button trigger?

First we select 'B' but since there are no 'B's during 2013 QlikView will loose the selection in field 'Year'!

Next we exclude the selection in field 'Category', fine we now have 'Category' = 'NOT B' but we no longer have the selection 'Year' = '2013'!

Example data that illustrates the problem could be;

Year	Category	Value
2012	A	1
2012	B	2
2012	C	3
2013	A	4
2013		6

If user did not make any selection prior to pressing the button we should get values 1, 3, 4 & 6.

If user select 2012 and press button we should get 1 & 3 (will work with button first selecting 'B' and then exclude selected value).

User selected 2013 and press the button I want values 4 & 6 (exclude those with 'B').

With button first selecting 'B' and then excluded values I will get 1, 3, 4 & 6 (since selection of Year is lost).

With button selecting all values but 'B' I will only get value 4 (since 6 does not have a Category).

In a listbox you can type 'NOT B' to do exactly this but what search pattern should I use in a trigger to accomplish this?

[#2]

I solved the same issue by first doing a select in Field (selecting B) and then doing a Select Excluded.

[anderseriksson]

Suggested and rejected as not working several times before.

I explain in the initial post why this won't work.

If you have rows with Null in the field those will be excluded too.

The Select Excluded will select the other possible values but last row in my small example will not be included.

[fchellaoui]

Hello,

This one worked for me. It is a single select in field based on concatenated & filtered possible values.

Regards

[anderseriksson]

What you are doing is a more complicated Not B selection.
You are not solving the original problem since you do have values in every row.

The last row in your inline table contains '' in field Category.
The problem is when field Category has value Null().

Besides, your solution was suggested by celambarasan in 2013 above (with minor variation).

[fchellaoui]

Hello Anders,

You're right.

We've had lots of issues related to undefined/null values in data.

As the QV engine works by indexing the tables different values, the absence of value is not working well at all.

Meaning: having Null values is not ok and data cannot be processed.

When we face such cases, we simply put a value when field=null() like 'NONE' or 'NA' or 'UNDEFINED' so that QV can manage it as a real value, then we use that as a data control tool and see if we can fix the values in the data source / system directly (cleaner).

Cleaning/formating the data is a necessary step before processing/analyzing it.

As for the 'Not B' selection, you can always put a variable in the Action expression so that the 'Not B' can be redefined by what you need.

Also added a clear field selection in the button so you don't need to clear the field yourself.

But then it starts to become a bit heavy :-).

Regards,

[adria]

Another thing you could try is to:
1. Deselect everything
2. In a listbox for Category, search -B
3. Create a bookmark that can be applied on top of current selections
4. Create a trigger to apply this bookmark

This trigger can be used in more dynamic situations than the standard two-step response. I'm concerned about how it would handle null values, though. You may need to throw in a null to space (' ') map in the script for this field.

[anderseriksson]

That was the solution used four years ago and is still the solution.
But I don't like it as using bookmarks this way easily breaks something.

[Anonymous]

Did you try just action with select in fileld like this: ='<>B' ?

[anderseriksson]

Did you try it?
It does not select the rows with Null values which is the original problem.

[Anonymous]

Yes, I did.

I often use it with built-in function NullAsValue in script.

Example from guy below.

I don`t think that it is a good solution, however I haven`t got any better