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:
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:
Once you have setup your variables, select “Tools – Edit Module” from the menu bar, then create the following macro:
Set TableBox = ActiveDocument.GetSheetObject( "TB03" )
For RowIter = 0 to TableBox.GetRowCount-1
For ColIter = 0 to TableBox.GetColumnCount-1
If RowIter = 1 Then
set cell = TableBox.GetCell(1,ColIter)
If ColIter = 0 then
ActiveDocument.Variables("vPlayer").SetContent cell.text, True
If ColIter = 1 then
ActiveDocument.Variables("vFPTS").SetContent cell.text, True
If ColIter = 2 then
ActiveDocument.Variables("vPassAtt").SetContent cell.text, True
If ColIter = 3 then
ActiveDocument.Variables("vPassYd").SetContent cell.text, True
If ColIter = 4 then
ActiveDocument.Variables("vPassTD").SetContent cell.text, True
If ColIter = 5 then
ActiveDocument.Variables("vInt").SetContent cell.text, True
If ColIter = 6 then
ActiveDocument.Variables("vNFLTeam").SetContent cell.text, True
If ColIter = 7 then
ActiveDocument.Variables("vYear").SetContent cell.text, True
If ColIter = 8 then
ActiveDocument.Variables("vWeek").SetContent cell.text, True
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):
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:
Next, setup a trigger at the document level. On the menu bar, click on “Settings – Document Properties”, then go to the “Triggers” tab:
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:
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:
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:
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.