Saturday, February 23, 2013

Macro Copies Value From a Chart into a Variable



Here's an example of a method that will allow a QlikView macro to look at a value inside a sheet object like a straight table and put the value into a variable. Then the variable can be used in an expression like a text box or small chart. Most of the time, something like this could be accomplished with an expression in the variable that produces the same value as what you see in the chart. But a macro would be useful when duplicating the chart value with an expression would be difficult because of varying sort sequences or calculated dimensions in the chart, etc.

This is the macro code:

Sub Get_XTOTAL
 Application.WaitForIdle
 set cell=ActiveDocument.GetSheetObject("CH45").GetCell(1,7)
 ActiveDocument.Variables("vXTOTAL").SetContent cell.text, True 
 set cell=nothing
End Sub

This example macro is extracting a value from the totals row of the chart where the object ID is CH45 (look in the General tab of the chart properties to find the object ID). The number arguments for the GetCell method are row and column; row 1 and column 7 in this example.
Then the macro updates the value in the document variable named vXTOTAL. When written as in this example, the variable must already exist, so create the variable before executing the macro.
When I use a macro like this, I set up a trigger to execute the macro as needed, usually when a selection changes.

★ ★ ★

3 comments:

nmust said...

Hi,

Have you ever written a macro to insert the data held in an input box into the chart via dynamic update. Basically adding rows to existing chart and when saved becomes part of the data model.

Thanks - Nick

-TB said...

Hi Nick,

I have done that but it was work done for a client and not available to be posted here - sorry. You should put your question to QlikCommunity.

-Tim

Anonymous said...

i have one question for you,

if the table is having multiple rows and columns and i want one full row and column from the table instead of one cell, then what would be syntax for this and then how can i store the selected rows and columns to variable which can be used in table box?