Qlikview Macro to Capture Table Box Fields

Recently, I was building a Qlikview application and wanted to make the first row of a table box stand out to the user.  I felt the user needed to have an easy way of seeing the content of the first row, so they wouldn’t glance over it due to the abundance of objects on the page.

Therefore, I decided to display the field values of the first row in a series of text box objects.  After playing around a little while, I came up with a Qlikview macro to capture table box fields in variables, which could then be used for different purposes.  In my case, displaying the values in text box objects.

For demonstration purposes, let’s say there is the following table box:

Qlikview Macro to Capture Table Box Fields

This example displays fantasy football statistics for QBs, sorted in descending order by FPTS (fantasy points).  Simple enough!

What I want to accomplish is making the leader for the selected week (in this case week 1) stand out to the user.  Matt Ryan had the most fantasy points, so I came up with a macro to capture the field values in the top row of the table box into variables, which I then display in text box objects to the user.

Implementing a Qlikview Macro to Capture Table Box Fields

First of all, setup variables to store the table box field values you would like to capture.  Based on my table above, I setup the following variables:

  • vPlayer
  • vFPTS
  • vPassAtt
  • vPassYd
  • vPassTD
  • vInt
  • vNFLTeam
  • vYear
  • vWeek

Once you have setup your variables, select “Tools – Edit Module” from the menu bar, then create the following macro:

It is especially important that you update the following in the above script to be reflective of your own environment (text highlighted in Red):

  • Set TableBox = ActiveDocument.GetSheetObject( “TB03” )
  • Update your variables to reflect the field names in your table box (going left to right in order)

Furthermore, setup your text box objects to display the variable values.  In my example, I setup text box objects for the variables (right column below), and added text box objects associated with each for field labels (left column below):

Qlikview Macro to Capture Table Box Fields

 

In the properties for each text box object (ignore the text boxes that display the field label), you can then assign the variable for which you want to display the value:

 

Qlikview Macro to Capture Table Box Fields

 

Trigger Setup

Next, setup a trigger at the document level.  On the menu bar, click on “Settings – Document Properties”, then go to the “Triggers” tab:

 

Qlikview Macro to Capture Table Box Fields

Select the Document Event Trigger “On Any Select” and click the Edit Actions button.  Add an action Type “External – Run Macro”, and key in the Macro Name “GetFieldValues” that you setup earlier:

Qlikview Macro to Capture Table Box Fields

That should do it!

Finally, to make sure it is working properly, filter your Qlik app to change the top record in your table box.  In my example, because I selected Week 2, now Aaron Rodgers is the top QB for that week:

Qlikview Macro to Capture Table Box Fields

As a result of that selection, my text box objects update accordingly to reflect the new variable values based on the top row of the table box:

Qlikview Macro to Capture Table Box Fields

 

I hope you found this post useful and I encourage any comments or feedback.  Furthermore, I encourage you to modify the code to suit your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

2 thoughts on “Qlikview Macro to Capture Table Box Fields

  1. Hi

    How to setup variables to store the table box field values in qlikview.

    vPlayer
    vFPTS
    vPassAtt
    vPassYd
    vPassTD
    vInt
    vNFLTeam
    vYear
    vWeek

    • Hello Ashwin. To create a variable, go to “Settings – Variable Overview…”, then click on “Add” and type the name of the first variable (i.e. “vPlayer”), then click “OK” to create the variable. Repeat for each of the variables I mention in the post and you should be all set.

Leave a Comment