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”
Once the Table Boxes are aligned, minimize each and set them up vertically.
At this point, your QVW should look like this:
Next, setup the following Variable:
Add a new variable “vPosition”
With following definition:
Next, go into Tools –> Edit Module, and add the following Sub routine:
Add the code below:
GetPosition = ActiveDocument.Variables("vPosition").GetContent.String
set oQB = Activedocument.GetSheetObject("TB01")
set oRB = Activedocument.GetSheetObject("TB02")
set oTE = Activedocument.GetSheetObject("TB03")
set oWR = Activedocument.GetSheetObject("TB04")
IF GetPosition = "QB" THEN
MSGBOX "You selected QB"
ELSEIF GetPosition = "RB" THEN
MSGBOX "You selected RB"
ELSEIF GetPosition = "WR" THEN
MSGBOX "You selected WR"
ELSEIF GetPosition = "TE" THEN
MSGBOX "You selected TE"
MSGBOX "You must select an item in the Positon List Box"
Change the following settings:
Now we’re ready to assign the macro to a field level event. Go into “Settings –> Document Properties”
In the “Triggers” Tab, under the Field Event Triggers section, click on “Add Action(s)”
Add a new Action with Action Type “External”, Action “Run Macro”
Assign the macro created above, click “OK” then “Apply”
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:
Now, select “TE” from the Position List Box. The Table Box titled “TE” is now visible, and the Table Box “QB” is minimized:
Now, unselect “TE” from the Position List Box. You are prompted with a message and all Table Boxes are minimized:
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!