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?

[Anonymous]

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

[anderseriksson]

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

[CELAMBARASAN]

Hi,

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

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

[anderseriksson]

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

[#6]

Maby I am wrong, but would this work?

Cheers

Martin

[kaushiknsolanki]

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

[anderseriksson]

Sorry, does not work.

Select excluded only works if the value is first selected.

/anders

[anderseriksson]

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

[#10]

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