Friday, November 21, 2008

Copying Variables From One QlikView Document to Another


Today, one of the guys at work was trying to figure out how to transfer all of the document variables from one QlikView document to another. We discussed some of the things we've done in the past: loading variables and values from a spreadsheet or database either through the loadscript or through macro code. We even have an older application that stores variables in a table with loadscript code and then the document is used as a "binary" in another document's loadscript and the second document turns the table values back into variables. But, none of those options seemed to fit the requirement. One obstacle is that we could not figure out an easy way to export all of the variables and values from a document. You can export the expressions from the Expression Overview but the Variable Overview has no similar function.

With some help from Lars at Brait AB (a reader of this blog, Thanks, Lars!) who suggested a way to loop through the variables, I tested the VBScript below that can transfer variables from one document to another. I created a small text file on my laptop named "tbtest.vbs" and typed this code into it:
' /* Test Moving a variable from one QV document to another */
' /* QlikView Maven, Nov 2008 */
Option Explicit
Dim objQV, objSource, objDest, objSourceVar, objDestVar
Dim objVars, varcontent, objTempVar, varname, i

'initialize
Set objQV=CreateObject("QlikTech.QlikView")
Set objSource=objQV.OpenDoc("C:\QVfolder\Source_rpt.qvw")
Set objDest=objQV.OpenDoc("C:\QVfolder\Dest_rpt.qvw")
set objVars = objSource.GetVariableDescriptions

'Loop through the variables
for i = 0 to objVars.Count - 1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name)
  Set objSourceVar=objSource.Variables(varname)
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)


  'update the value of variable in Destination document
  Set objDestVar=objDest.Variables(varname)

  If objDestVar is nothing then
    'must need to create variable
    objDest.CreateVariable varname
    Set objDestVar=objDest.Variables(varname)
    End If

  objDestVar.SetContent varcontent,true


next 'end of loop

'save Destination document if desired
objDest.Save

'we're done, close down
objSource.CloseDoc
objDest.CloseDoc
objQV.quit
WScript.Quit


In order to run it, I just double-clicked on the tbtest.vbs file on my desktop. You can see the pathname to the two QlikView document files in the code. This script successfully copied the variables and their values from the source to the destination document.
If you omit the part of the script that opens and updates the destination document you could turn it into a script that lists out all of the report variables and their contents -- something that is not available with the regular variable overview.

6 comments:

Lars said...

Hi!

You could try something like this to find all the variables.(Macro code)

set vars = ActiveDocument.GetVariableDescriptions
for i = 0 to vars.Count - 1
set v = vars.Item(i)
rem Do something with the variable
msgbox(v.Name)
next


I hope this will help.
Kind regards
Lars at Brait AB

uday said...

Hi,
Can we do this without the use of macro?

-TB said...

Hi uday,

Note that the macro described above is a Windows macro and not a QlikView macro (although it could easily be modified to run as a QV macro).
I don't know of any tools that will let you copy the variables without some kind of macro-like code. You would need a tool that can use the QlikView object model to get at the variable definitions from both the source document and the destination document.
Let me know if you find something that works.
-Tim

RustyFishBones said...

Hi Tim,

You just saved me a whole load of pain, my file got currupted, I had a -prj folder, but when I reloaded the file all my variables were blank.

I had an older version, but all the same variables, so I was able to copy them from old to new
and it worked perfectly.

Thanks

Alan

-TB said...

The problem with -prj files not storing the variable values is the main reason my company hasn't started a standard process of version control archiving documents using -prj. We use so many variables that saving a document without the variable values is useless. I wish the clearing or not clearing variable values was optional.

-TB said...

Note: Our sysadmins at work have tightened up on security enough so that .vbs scripts will no longer run on company laptops. To get around it when I need to copy variables, I either use the QlikView macro version of the script, or, do my variable copying on an available server where .vbs scripts are still allowed to run.