topic On Sheet Activate> Select Multiple Values in a Fields in QlikView

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 00:57:54 GMT

I attempting to set up a sheet where 'On Sheet Activate' multiple values for a field can be selected in the search string.

For example. Triggers> Select in Field> salesrep_id > Search String> =1022, 3022, etc...

I have tried many combination's of () and keywords in the search string. I have yet to find the correct combination/syntax. Selecting one value works fine but multiple values does not.

The last entry in the below post is exactly what I am after.

http://community.qlik.com/forums/p/16933/66257.aspx

I would also be okay with the data being displayed within a pivot table. If I want an expression to show only: salesrep_id = 3202 AND 1022 AND etc..

The second of the two events would be the most useful. Thanks for any ideas you have to offer.

On Sheet Activate> Select Multiple Values in a Fields

prieper — Tue, 08 Dec 2009 03:54:21 GMT

If you wish to make your selections manually, have the Cntrl-button pressed whilst qlicking or on pressing Enter. This adds current selection to the previously made one. If you wish to create a macro, please have a look into the API-Guide and create a macro on activate:

rem ** cancel selection of first selected value **
set f = ActiveDocument.Fields("Class")
set fv = f.GetSelectedValues
fv.RemoveAt 0
f.SelectValues fv

rem ** select two non-consecutive field values in field **
set f = ActiveDocument.Fields("Class")
set fv = f.GetNoValues 'empty array
fv.Add
fv.Add
fv(0).Text = "Group"
fv(0).IsNumeric = false
fv(1).Text = "Field"
fv(1).IsNumeric = false
f.SelectValues fv

HTH
Peter

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 04:34:16 GMT

No I would just simply like to select value A and value B within the same field on sheet active.

If the macro is the only way this can be done I will find a different option like the pivot table. If I could make an expression: '=salesrep_id = 1022 AND 3027' then this would be sufficient but once again I am unsure of the syntax.

Thanks.

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 05:21:27 GMT

Are the salesrep_ids you list static? Will you always be selecting the same ones? If so, use Set Analysis on your expressions.

Sum({<salesrep_id = {1022, 3027}>} Sales)

You could use a variable to store the rep ids, like:

Sum({<salesrep_id = {$(vRepIDs)}>} Sales)

If you really want the selections to be made, then I think you need a macro as Peter suggested. I don't think it's possible to put multiple values into the Select Trigger. I'm back on 8.5, so I can't test it out. You could use the ToggleSelect which means keep the current selection and also select. So you could set 1022 to Select and then 3027 to ToggleSelect (use ToggleSelect for any additional).

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 05:37:37 GMT

I would like a pivot table to display just sales for salesrep_id equal to 1022, 3027, etc. (We have some split accounts with other salesmen, I would like to display) I do not need to compare it to anything or need to use Set Analysis, this may be further down the road. Currently I just want only data for sales id 1022 and 3027 to show in my straight/pivot table. Yes the sale id are static and I do not need to 'select' them just reduce data based on the values.

Is this the best option? Would data reduction be better to use? I do not want other salesmen to see other salesmen data. Am I taking the wrong approach. I would then make a tab/sheets for each salesmen and use section access to control which tabs they can see and can not see.

Thanks once again.

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 05:37:41 GMT

I just made an expression in my pivot table as 'salesrep_id = (1022) OR salesrep_id = (3202)' this returns a -1 value for the correct sales rep. How do I know only show the values that are equal to -1?

Thanks once again.

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 23:00:52 GMT

On Sheet Activate> Select Multiple Values in a Fields

Tue, 08 Dec 2009 23:33:28 GMT

Use Set Analysis in all of your expressions in the pivot.

Sum({<salesrep_id = {1022, 3027}>} Sales)

Once it is placed in each expression, the pivot will only display values for those two IDs.

On Sheet Activate> Select Multiple Values in a Fields

Wed, 09 Dec 2009 05:47:09 GMT

I used data reduction based on salesrep_id. This was the best solution.I made an Excel sheet which stores the ID.

Now I just need to figure out adding AD support for the names...

On Sheet Activate> Select Multiple Values in a Fields

Wed, 09 Dec 2009 05:50:44 GMT

Through it should be noted NMiller solution also worked. Using DDR just worked better for me. Through I will use the section access solution in the future I am sure.

Thanks for your help on this matter.

On Sheet Activate> Select Multiple Values in a Fields

Wed, 15 Dec 2010 00:00:45 GMT

I had exactly the same issue and the ToggleSelect feature worked perfectly. Thank you NMiller!

On Sheet Activate> Select Multiple Values in a Fields

jarno_loubser — Mon, 17 Jan 2011 13:26:50 GMT

I was able to achieve this by first performing a select in field (on sheet activation) of the one value in the list I do not want and then performing a select excluded (also on sheet activation).

I was lucky though in that only one value wasn't in my desired list.

Re: On Sheet Activate> Select Multiple Values in a Fields

Mon, 29 Oct 2012 18:43:08 GMT

Thank you very much for the clue on how to issue multiple fields on a sheet activation ! The Select In Field and followup of Toggle Select(s) works perfect.

Regards,

Rick

Re: On Sheet Activate> Select Multiple Values in a Fields

Mon, 29 Oct 2012 18:43:15 GMT

Thank you very much for the clue on how to issue multiple fields on a sheet activation ! The Select In Field and followup of Toggle Select(s) works perfect.

Regards,

Rick

Re: On Sheet Activate> Select Multiple Values in a Fields

Mon, 29 Oct 2012 18:43:41 GMT

Thank you very much for the clue on how to issue multiple fields on a sheet activation ! The Select In Field and followup of Toggle Select(s) works perfect.

Regards,

Rick

Re: On Sheet Activate> Select Multiple Values in a Fields

Mon, 29 Oct 2012 18:45:01 GMT

Thank you very much for the clue on how to issue multiple fields on a sheet activation ! The Select In Field and followup of Toggle Select(s) works perfect.

Regards,

Rick

Re: On Sheet Activate> Select Multiple Values in a Fields

Wed, 26 Feb 2014 20:40:27 GMT

Ryan,

I found your answer to be the best. Along with Neil Millers

Step by Step Instructions:

Open the properties of your sheet.

Step 1 - Within the Sheet Object Even Triggers - click "Add Action" , click "Add", then select "Selection" and then select "Select in Field" and click "OK"

Step 2 - Select your Field that you want to filter on (or type it), and then type in the data within the search string (example sales id - 1234)

Step 3 - Then Within the Sheet Object Even Triggers - click "Add Action" , click "Add", then select "Selection" and then select "Toggle Select" and click "OK"

Step 4 - Select your Field that you want to filter on (or type it), and then type in the data within the search string (example sales id - 5678)

I would also added a Lock Field within the Sheet Object

and subsequently added an Unlock Fields on the "OnLeaveSheet".

Regards,

Paul