topic MACRO - How to select a specific value in Listbox on Sheet Selection in QlikView

MACRO - How to select a specific value in Listbox on Sheet Selection

Thu, 15 Oct 2009 03:02:02 GMT

This is my first time using macros in Qlikview (and macros altogether in a very long time) I have a simple question regarding a macro when selecting a sheet.

I would like a specific listbox with years to be automatically selected with the latest year.

So on the OnActivateSheet Trigger, i have a macro which clears the selection:

ActiveDocument.Fields("Year").Clear

How would I get the field to be selected with the latest year?

The 'Year' field is in a 'Dates' table which has been retrieved on the load.

Hopefully somebody can help.

Thanks!

-Mike

MACRO - How to select a specific value in Listbox on Sheet Selection

biester — Thu, 15 Oct 2009 03:19:43 GMT

It's really essential in this case to know which version you are using, because with QV 9 there's no need for a macro.

Rgds,
Joachim

MACRO - How to select a specific value in Listbox on Sheet Selection

biester — Thu, 15 Oct 2009 03:23:19 GMT

Oh, I just saw that you are using the term "Trigger", so I assume you HAVE QV 9.

So:

OnActivateSheet Trigger is alright; BUT:

as action don't use "External/Run Macro" !!!! Use "Selection/Select in Field". There, for "field" you specify "Year", for "Search string" you specify "=max(Year)". That's all and it should run without macro programming.

Rgds,
Joachim

MACRO - How to select a specific value in Listbox on Sheet Selection

Thu, 15 Oct 2009 03:35:23 GMT

Thanks for the quick reply!

Actually I am using QV 8.5 so I don't see the "Selection/Select in Field". On the Macro tab, I see Sheet Event Triggers: OnActivateSheet and OnLeaveSheet.

MACRO - How to select a specific value in Listbox on Sheet Selection

Thu, 15 Oct 2009 04:28:55 GMT

Hi,

in this case i first create a variable that hold max year: =max({1} Year)

(lets say that this variable is named vMaxYear). Variables menu is in Settings --> Variable overview

next macro part:

1. Clear Year field (you already achieve this)

2. get content of vYear: set maxYear = ActiveDocument.GetVariable("vMaxYear")

3. Make selection in Year field: ActiveDocument.Fields("Year").Select maxYear.GetContent.String

And put the created sub in OnActivateSheet or OnLeaveSheet

Regards!

Stefan

MACRO - How to select a specific value in Listbox on Sheet Selection

Anonymous — Thu, 15 Oct 2009 09:08:55 GMT

This is correct, but there is no need for varaible if use Evaluate:
ActiveDocument.Fields("Year").Select ActiveDocument.Evaluate("max(Year)")

MACRO - How to select a specific value in Listbox on Sheet Selection

rwunderlich — Thu, 15 Oct 2009 10:01:23 GMT

Or TopSelect.

ActiveDocument.Fields("Year").TopSelect "Year", 1

-Rob

MACRO - How to select a specific value in Listbox on Sheet Selection

Thu, 15 Oct 2009 21:12:04 GMT

Both of the last 2 suggestions work for me. I could not get Stefan's to work but that is probably my fault.

However these solutions work to select the max year as long as the year is already selected. For example just say I have 3 years: 2007, 2008, 2009.

Before I select the sheet if:

1) 2007,2008,2009 are selected --> 2009 becomes selected

2) none are selected --> 2009 becomes selected

3) 2007,2008 are selected --> 2008 becomes selected ... however ideally I would like 2009 to becomes selected. Does anybody know how to achieve that?

Anyways the solution still works for me as the main thing is one year needs to be selected on this sheet.

Thanks everybody for your help!

-Mike

MACRO - How to select a specific value in Listbox on Sheet Selection

Anonymous — Thu, 15 Oct 2009 23:55:50 GMT

Mike, if you use "Evaluate", simply change expression to
("max(all Year)")
It will ignore selections

MACRO - How to select a specific value in Listbox on Sheet Selection

Fri, 16 Oct 2009 01:10:00 GMT

That works! wow that was so simple.. Thanks!

MACRO - How to select a specific value in Listbox on Sheet Selection

Mon, 16 Nov 2009 15:52:29 GMT

Sorry to utlize this thread, but I was struggling with the same sort of issue, but I can't seem to make this work.

I am using version 9, and tried what biester suggested: Document Properties > Triggers > OnOpen > Select in field.

In Field I have "[AP Date]" (stands for Accounts Payable date), and Search String "= Max ([AP Date])", but this does not choose the latest date in the list. What is wrong?

Thanks, - TP

MACRO - How to select a specific value in Listbox on Sheet Selection

rwunderlich — Tue, 17 Nov 2009 08:50:46 GMT

Does it select any value? Can you post the qvw?

-Rob

MACRO - How to select a specific value in Listbox on Sheet Selection

Tue, 17 Nov 2009 19:46:38 GMT

Hi,

I'm also struggling with this. I´m trying to use the new functionality with triggers instead of using macros in order to improve the performance.

I'm having a variable generated in the script containing a Period. I then want to use this variable in my trigger in order to select a Year Month by default in the chart.

I´m trying to use the activity "Choose in field" and then use my variable as a search string to my field. For some reason this doesn´t work. It works fine if I hardcode a Period instead of using my variable.

Any idea anybody?

Regards

MACRO - How to select a specific value in Listbox on Sheet Selection

Tue, 17 Nov 2009 19:56:49 GMT

Here are the Trigger properties (OnOpen action): nothing is selected.

MACRO - How to select a specific value in Listbox on Sheet Selection

Tue, 17 Nov 2009 20:09:19 GMT

Perfect ! Thanks!

It works fine!