topic Macro to clear variables in QlikView

Macro to clear variables

Thu, 22 Apr 2010 17:22:38 GMT

I have a QVW like a financial statement and i have added a formula which links to a variable where i enter for example 25.00 and the financial statement will automatically adjust one line item according to the 25.00 i entered in a varaible. The 25.00 is linked to the Rate. The current rate is 20 but i have added a formula where if i change the varaible to 25.00 then the revenue will change accordingly.

What i need to be able to do is run a macro which will clear the variable from 25.00 to 0.00 when needed. I have multiple rates for various line items so i need to be able to clear them rather than having to go back and enter 0.00 in each variable.

Revenue = 100

Membership = 5

Rate = 20.00

New After 25.00

Revenue = 125

Membership = 5

Rate = 25.00

Thanks,

Krishan

Macro to clear variables

Thu, 22 Apr 2010 18:43:18 GMT

Hi Krishan,

This macro will reset all variables to 0.0

sub reset_vars

set

doc=ActiveDocument

set

for

set

v = vars.Item(i) "0.0" , true

set

v = nothing

set

vars = nothing

set

doc = nothing

end

sub Kind regards i = 0 to vars.Count - 1 vars = doc.GetVariableDescriptions

Huub

Macro to clear variables

Thu, 22 Apr 2010 18:48:21 GMT

What if i only want to reset selected variables? I have other variables which should not be touched because they feed other formulas.

Macro to clear variables

Thu, 22 Apr 2010 19:34:54 GMT

When the variables you want to reset start with the same prefix. like PAR_rate, PAR_index etc.. You can add a selection around the reset-part within the for/next loop.

if mid(v.Name,1,3)= "PAR" then

doc.Variables(v.Name).SetContent

"0.0", true

end if

Huub

Macro to clear variables

Thu, 22 Apr 2010 21:09:44 GMT

Ok this might work because the variables which i would like to change to 0.00 all begin with v_NEW. I am not familiar with Macros and how to set them up or where to paste this formula you have created?

Thanks again,

Really appreciated it!!!!

Krishan

Macro to clear variables

Thu, 22 Apr 2010 22:03:41 GMT

I was able to use this formula:

SUB

"v_NewCommRate"

).SetContent "0.00", true

"v_NewPRate").SetContent "0.00", true

"v_NewHRate"

).SetContent "0.00", true

"v_NewMRate"

).SetContent "0.00", true

END SUB

Macro1

Macro to clear variables

Thu, 22 Apr 2010 22:11:17 GMT

Ok,
Open your document.
open macro-editor (Menu Tools, edit Module).
Copy/past the code below.
Press Ok.
In your document insert a button. (New sheet-object, button).
Give it a text and ten press tab actions.
press add, action type = external, click run-macro , press Ok and fill in the macro-name: reset_vars
Now you have a button, that will run the macro
Huub

sub reset_vars
 set doc=ActiveDocument
 set vars = doc.GetVariableDescriptions
 for i = 0 to vars.Count - 1
 set v = vars.Item(i)
 if mid(v.Name,1,5) = "v_NEW" then
 doc.Variables(v.Name).SetContent "0.0", true
 end if
 next
 set v = nothing
 set vars = nothing
 set doc = nothing
end sub