topic Re: Button doing a NOT value selection in field? in QlikView

Button doing a NOT value selection in field?

Anders_Eriksson — Fri, 22 Mar 2013 16:02:11 GMT

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?

Re: Button doing a NOT value selection in field?

Anonymous — Fri, 22 Mar 2013 17:27:06 GMT

Anders,

Try this:
1. Lock All
2. Unlock field Category
3. Select in field Category, value B (nothing happens if it is not available)
4. Select excluded
5. Unlock all

See if makes sense.

Regards,
Michael

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Mon, 25 Mar 2013 07:59:50 GMT

Yes I thought of that but while it gets the correct results it won't say 'Category = NOT B' which is important to me.

There are that much data that end users need this selection to be sure all the B's are excluded.

It's a question of clarity to the end user, explaining to them that there are no B's at all if the above selection is absent is not sufficient.

Since it is possible doing this selection manually it should also be possible doing it in a trigger during the same circumstances.

/anders

Re: Button doing a NOT value selection in field?

CELAMBARASAN — Mon, 25 Mar 2013 08:17:19 GMT

Hi,

You can use this as Select in field expression for the field value

='('&Concat({<Category-={'B'}>}Value,'|')&')'

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Mon, 25 Mar 2013 08:46:43 GMT

No I can't, that would select all other Category values but 'B'.

Then value number 6 would also be excluded since that does not have any Category.

/anders

Re: Button doing a NOT value selection in field?

Mon, 25 Mar 2013 09:01:20 GMT

Maby I am wrong, but would this work?

Cheers

Martin

Re: Button doing a NOT value selection in field?

kaushiknsolanki — Mon, 25 Mar 2013 09:02:24 GMT

Hi,

Why dont you create a seperate Category field from Script, which will not have the 'B' as value, then you can use this field in your front end for selection.

Hope this will help.

Regards,

Kaushik Solanki

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Mon, 25 Mar 2013 09:23:03 GMT

Sorry, does not work.

Select excluded only works if the value is first selected.

/anders

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Mon, 25 Mar 2013 09:31:06 GMT

Well it's not only 'B' that I want to be able to exclude this way and I'm not to happy about having a lot of fields CategoryWithoutB, CategoryWithoutC and so on.

And for end user clarity I want the selection to say 'Category = NOT B'.

Another reason is when Category is in a linked dimension table aside of the central facts table.

Having a field CategoryWithoutB in the dimension table would then select rows in the dimension table, rows in the facts table without any corresponding dimension rows would then be excluded also.

That is the same problem as in my little example when there is no category value.

The CategoryWithoutB field would then need to be added to the facts table which is not what I want.

Add to that the posibility of more than one facts table and it is a no go solution.

I DO want to make the selection 'Category = NOT B' in a trigger!

/anders

Re: Button doing a NOT value selection in field?

Thu, 20 Jun 2013 10:40:43 GMT

I'm currently having the same issue, did you ever find a solution to your problem?

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Thu, 20 Jun 2013 14:35:28 GMT

Sorry but no, only resolution to this problem was by making my selections manually and then save them as a bookmark.

The bookmark could then be activated from the button.

Not a perfect solution but what I could achieve.

Re: Button doing a NOT value selection in field?

Mon, 24 Jun 2013 07:41:01 GMT

Hi,

To make it say 'NOT B' there need to be a couple of values more than 3 or change the setting MaxCurSelEntries to a lower value.

Otherwise it will only say "A, C, D, E ..." instead of 'NOT B'. To change the MaxCurSelEntries, goto Help/About QlikView and then

rightklick on the Qlikview symbol in the lower left corner.

Attached is a solution with trigger (ON RELOAD) and on a button.

Cheers

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Wed, 26 Jun 2013 07:42:54 GMT

Interesting, never knew of those settings available by rightclicking the QlikView logo.

But I suspect this is not a solution either?

First, my real world problem (not the example above) had more than 3 values to start with and
second, changing of those settings I suspect affect the client not the application,
if that is correct one would have to change the setting for every user including when new users are introduced.

I suspect the problem is solveable by using a maceo triggered by the button but I don't want to go that way even though I have the knowledge how to do it.

Re: Button doing a NOT value selection in field?

Wed, 26 Jun 2013 08:01:15 GMT

Those settings are available for everyone if you set the value in the load script.

For example:

SET maxCurSelEntries = 1;

The macro can be triggered when someone opens/reloads/whatever the document by using triggers. (Settings/Docment properties/Triggers)

My last post showed both a trigger and a button. But solving it without a macro is not possible i think.

I have a request with QlikView for a NOT-selection. Go vote if you like it:

http://community.qlik.com/ideas/2756

Cheers

Re: Button doing a NOT value selection in field?

Anonymous — Wed, 10 Jun 2015 06:20:29 GMT

Hi , Anders Eriksson

I was having the same query related to selecting " NOT IN " field using a button ,

so i figured it out and this may be a solution to your query as well.

Hope it helps .

Cheers!! !

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Thu, 11 Jun 2015 07:01:42 GMT

Thanks for your reply but you did not read my original posting through.

I have already walked you through this solution and explained why it does not work!

> 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?

Re: Button doing a NOT value selection in field?

Anonymous — Tue, 27 Oct 2015 11:11:31 GMT

Hi, thought this is not a perfect solution, but I think this do get the result. Thanks

Re: Button doing a NOT value selection in field?

Fri, 02 Sep 2016 14:00:31 GMT

Hi Anders,

From your initial question it sounds like you know up front that "B" is the only category that you want to be able to exclude at the click of a button.

If that is the case we can prepare a new field in the script that will help us.

example_stg:

LOAD * INLINE [

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

example:

LOAD *
,if(Category<>'B','Category is Not B') as excludeB
Resident example_stg;

DROP Table example_stg;

Then you can simply use a Listbox on the excludeB field (my preference) or you can create an action to put in a button or on a trigger.

The action would simply be Select Possible against excludeB.

Here is an example:

Re: Button doing a NOT value selection in field?

Anders_Eriksson — Fri, 02 Sep 2016 15:31:05 GMT

Already suggested by kaushik.solanki and rejected as solution.

Would need one extra field for each value that should be excluded and depends on those values being known at load time.

Re: Button doing a NOT value selection in field?

Mon, 05 Sep 2016 09:42:45 GMT

Ah, sorry I missed that.

You could do something quirky with calculated dimensions.

If you create a separate table just to hold a copy of the Category values you could then create a list box on it whose selections would become immune from mutual exclusivity issues with other selections.

This could then be used in a calculated dimension to determine whether or not to include the row. I've called it [Category NOT IN] in the example to get you the clarity you were after to make it obvious in the current selections box that things are being excluded. I also coloured the list box reddish to try and give a visual clue that it is treated differently.

The calculated dimension checks the selections and returns either 1 or Null() and is set to suppress nulls. It is also hidden.

This certainly works for this simple example but the logic would need to be propogated in other charts and so on in the report where the exclusion is to be applied. You also need to make sure you choose a big enough max values number in the GetFieldSelections function to cover all the possible selections else you would end up with weirdness when the selection switched to the 'NOT B,C' or 'X of Y' type of response.

This is probably more trouble than it is worth for more complicated or larger production reports so I'm just presenting it as a curiosity. Hopefully thought provoking though.

By the way, if you use the GetFieldSelections function you can specify a max values without needing to tinker with any global client settings.

Cheers

John