Qlikview Macro Field Event Trigger

This post demonstrates how to dynamically display a specific object based on the value selected in a List Box, using a Macro and OnSelect Field Event Trigger.  I recently needed to figure out how to split a table into smaller views so a user could see the data in a more user-friendly display.  All the data related to the same category, but the underlying table was large and fields within the table had specific meaning based on a category field within the table.  By splitting up the table, the user was able to get a better view of the data, based on selecting a specific field value in a List Box.  I displayed one Table Box at a time based on this selection, saving space in the QVW UI.

For the purposes of this example, I’m creating four simple Table Box objects, to illustrate the concept.  The data table used for this example can be found at the end of this post.  You can save this table as an Excel file, then load the data into a new QVW file and follow the steps below.

Setup for Qlikview Macro Field Event Trigger

Add a List Box for field “Position” to your QVW.

Next, create four Table Box objects, each containing the following fields:

  • Table Box 1:  Player, Position, PassAtt (In Properties, put in the Title “QB” for the Table Box)
  • Table Box 2:  Player, Position, RushAtt (In Properties, put in the Title “RB” for the Table Box)
  • Table Box 3:  Player, Position, Tar (In Properties, put in the Title “TE” for the Table Box)
  • Table Box 4:  Player, Position, Recpt (In Properties, put in the Title “WR” for the Table Box)

When each Table Box is visible, the Top Left corner of each should be aligned.  To accomplish, make sure each Table Box is visible, select all four objects (hold Shift Key down and click on them), then select the following from the men:

First, select “Object –> Align Top”

Then, select “Object –> Align Left”

Qlikview Macro Display Object List Box Selection

Once the Table Boxes are aligned, minimize each and set them up vertically.

At this point, your QVW should look like this:

Qlikview Macro Display Object List Box Selection

Next, setup the following Variable:

DimLimitVariable1-1

Add a new variable “vPosition”

Qlikview Macro Display Object List Box Selection

With following definition:

Qlikview Macro Display Object List Box Selection

Next, go into Tools –> Edit Module, and add the following Sub routine:

Qlikview Macro Display Object List Box Selection

Add the code below:

Change the following settings:

ModuleScriptingSecurity

Now we’re ready to assign the macro to a field level event.  Go into “Settings –> Document Properties”

SettingsDocumentProperties

In the “Triggers” Tab, under the Field Event Triggers section, click on “Add Action(s)”

Qlikview Macro Display Object List Box Selection

Add a new Action with Action Type “External”, Action “Run Macro”

Qlikview Macro Display Object List Box Selection

Assign the macro created above, click “OK” then “Apply”

Qlikview Macro Display Object List Box Selection

Now you are ready to try it!

Select “QB” from the Position List Box.  You will see a message box appear, and the Table Box titled “QB” will now be visible:

Qlikview Macro Display Object List Box Selection

Now, select “TE” from the Position List Box.  The Table Box titled “TE” is now visible, and the Table Box “QB” is minimized:

Qlikview Macro Display Object List Box Selection

Now, unselect “TE” from the Position List Box.  You are prompted with a message and all Table Boxes are minimized:

Qlikview Macro Display Object List Box Selection

You get the idea.  As stated earlier, I used this technique to break a large table into smaller Table Boxes, giving the user a specific view of the data based on a given field.  This made the viewing experience better and forced me to learn some new Qlikview coding techniques!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Table used for this example:
PlayerPassAttRushAttTarRecptPosition
Matt Ryan43300QB
Matthew Stafford32400QB
Andrew Luck53500QB
Carson Palmer37400QB
Peyton Manning36400QB
Ben Roethlisberger34300QB
Russell Wilson28700QB
Drew Brees42100QB
Philip Rivers36200QB
Tom Brady56100QB
Tony Romo37000QB
Aaron Rodgers33000QB
Le'Veon Bell02176RB
Marshawn Lynch02011RB
DeMarco Murray02243RB
Matt Forte01798RB
Chris Johnson01355RB
LeSean McCoy02166RB
Arian Foster02722RB
Adrian Peterson02132RB
Frank Gore01600RB
Eddie Lacy01233RB
Jamaal Charles0744RB
Doug Martin0921RB
Julius Thomas0087TE
Vernon Davis0064TE
Greg Olsen00118TE
Martellus Bennett00108TE
Rob Gronkowski00114TE
Jimmy Graham00108TE
Antonio Gates00106TE
Travis Kelce0053TE
Tyler Eifert0033TE
Heath Miller0043TE
Jason Witten0062TE
Jordan Reed0011TE
Calvin Johnson00117WR
A.J. Green0196WR
Steve Smith00157WR
Antonio Brown0065WR
DeAndre Hopkins0054WR
Brandon Marshall00128WR
Julio Jones0097WR
Reggie Wayne00139WR
Jordy Nelson00149WR
Dez Bryant0064WR
Demaryius Thomas00114WR
Larry Fitzgerald0041WR

 

Leave a Comment