Macro to Copy Variables In From Another Document

My preference when I need to copy variables from one QlikView document to another is to use a small Windows script to do the copying (I wrote about it in 2008 - search on "Copying Variables From One QlikView Document to Another"). But, I know there are some people who can't use a Windows script because of corporate or I.T. rules. Here's a QlikView macro that you can insert into your module area that will copy in all of the variables from another document. 

This macro has you entering a pathname for the document into an input box. For anyone in the advanced class you can replace that with a browse-for-file feature if you think it is worth the time (I wrote about it - search on "Browse-for-File Macro Button"). In the next blog posting ("More on Copy Variables From Document to Another") I show a macro that can be installed once in a toolkit document and it will copy variables from a specified external document to another external document.

Here's the macro code. Try it out - you can copy and paste the text you see in this blog. Remember, as always, to make a backup copy of your document before testing macros like this. To run the macro, create a button for it or for one-time use just click the Test button in the module editor. There's no error checking but the most common error for this macro is entering an invalid pathname. If your source document requires a password then you will be asked for the password when the macro opens the source document.

Sub Copy_Variables
' /* Copy Variables In From Another QV Document */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn=inputbox("Enter pathname to source document containing variables you want to copy:","Enter Pathname for Source Document")
if trim(fn)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn) 
set objVars=objSource.GetVariableDescriptions
varctr=0

'Loop through the variables
for i = 0 to objVars.Count - 1
  varctr=varctr+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 current document
  Set objDestVar=ActiveDocument.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    ActiveDocument.CreateVariable varname
    Set objDestVar=ActiveDocument.Variables(varname)
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
msgbox varctr&" variables copied from "&fn
objSource.CloseDoc
set objSource=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables

★★★

Make a Copy of a Cycle Group

I sometimes build cycle groups with more than a dozen calculated dimension expressions and labels that are text expressions. It used to irritate me when I needed to make copies of those cycle groups (like when I need a very similar group for a chart with two or more dimensions or a separate group for a graph which may be a little different from a group used for a straight table). Copying the cycle group was a time-consuming chore going back and forth copying and pasting.

Here's a macro you can copy into the module code of a QlikView document that will allow you to easily make copies of a cycle group. I got the code for this macro originally on QlikCommunity and modified it to use input boxes to ask for the cycle group names. It has been been very useful - I usually just copy it into a document temporarily when I need to make duplicate cycle groups and remove the macro afterwards. As always, you should make a backup copy of your document before making major updates or running macros that do updates.

Sub RunCopyCycleGroup
'--------------------
CGName=inputbox("Enter name of cycle group to copy:")
if trim(CGName)="" then 'no entry or cancel
  exit sub
  end if
NewName=inputbox("Enter name for copy of "&CGName&":")
if trim(NewName)="" then 'no entry or cancel
  exit sub
  end if
Call CopyCycleGroup(CGName, NewName)
End Sub
'
Sub CopyCycleGroup(CGName, NewName)
'-----------------------------------
' /* Copy cycle group. OK if NewName exists. */
Set SrcGrp = ActiveDocument.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = ActiveDocument.GetGroup(NewName)
If NewGrp Is Nothing Then
  Set NewGrp = ActiveDocument.CreateGroup(NewName)
Else
  Set NewProps = NewGrp.GetProperties
  for i = 1 to NewProps.FieldDefs.Count
    NewGrp.RemoveField 0
    Next
  End If
Set NewProps = NewGrp.GetProperties
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps
msgbox CGName&" copied to "&NewName
End Sub

I have also posted a macro that will copy a cycle group from one document to another, separate document (search on "Macro to Copy a Cycle Group From One Document to Another").

★★★

Referring to a Column Label When the Label is a Text Expression

Last week, I added an enhancement to a QlikView document that had been in use for over two years. My task was simply to add a column to a straight table that would have an expression like this:  =[column1] - [column2]. It should be pretty simple. 
But the two columns I needed to refer to had text expressions using variables in their column labels. The two column labels looked like this:
=abv_PRICE&' '&lbl_Cmpr&' '&abv_EUAVG
=abv_COST&' '&lbl_Cmpr&' '&abv_EUSTD 

It took a few tries to get the syntax right. I didn't change the existing columns but I copy and pasted their label expressions into my column expression and then modified the syntax and added dollar sign expansion. The expression for my new column still refers to the two other columns using their column labels within square brackets; it looks like this: 
[$(='$(abv_PRICE) $(lbl_Cmpr) $(abv_EUAVG)')]-[$(='$(abv_COST) $(lbl_Cmpr) $(abv_EUSTD)')]

I hope this might help someone with a similar problem to solve. 

★ ★ ★

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.

★ ★ ★

Entire Load Statement in a Variable

QlikView document variables contain text. The software allows us to use variables in different situations and, for such a simple idea, they add considerable flexibility to help you satisfy requirements.
 
Most people use variables for field names, label text, expressions, and other relatively short bits of text. You can store longer text in a variable that may be useful. For example, I recently stored this entire bit of loadscript code in a variable named var_UOM_table:

/* Unit of Measure - loadscript code */
if NoOfRows('TBL_UOM_SELECT')>0 then
    Drop table TBL_UOM_SELECT;
end if
TBL_UOM_SELECT:
Add Load * inline
[Unit of Measure: uom_conv
Box: 1
Case: 0.1
Std Drum: (1/DRUMFCTR)
] (delimiter is ':');


In the loadscript I simply wrote the single line
$(var_UOM_table)

and that expanded to the entire segment of loadscript code when the document was reloaded.

This was useful to me for a document that loaded variable definitions from a database. Remember that variables can contain text expressions and accept arguments. Keep variables like this in mind for loadscript situations that require standard bits of text that may need to be repeated or shared between documents.

★ ★ ★

Always One Selected Value Really

I developed a document a few months ago that provided the user the ability to select which currency (Dollars, Euros, Rupees, etc) to use for presenting financial data. The expressions in the document did not work correctly unless one and only one currency was selected. Originally, I clicked on the Always One Selected Value option available in Listbox and Multibox properties. But there was a problem with the option sometimes turning itself off and the first indication of a problem is when users noticed glaringly incorrect financial quantities!

The problem with the Always One Selected Value option is that it can turn itself off when someone does a Reduce Data operation on the document. My team frequently does that to reduce document file size in order to email a document or store it in the version control system.

Here's an alternative way to accomplish the same thing. In my document, I wanted a single value from the CURRENCY field always selected. So, I set up an action to be executed based on the OnAnySelect or OnOpen events. Go to Settings-Document Properties and click on the Triggers tab. In the Document Event Triggers window click on OnAnySelect. In the Field Event Triggers window, scroll down to CURRENCY (use your field name, of course), click on it and then click the OnSelect Edit Action(s) button.

When the Actions dialog box opens, click the Add button, add a Select In Field action, type CURRENCY (use your field name) into the Field box, and then type this into Search String:

=if(GetSelectedCount(CURRENCY)=1,CURRENCY,'Euro')

That expression makes sure only one value is selected and, if it isn't, it automatically chooses a default value - in this case it chooses Euro as a default currency value. You could also use a variable or an expression for the default value.


Click OK and then do the same thing for the OnOpen event. If needed for your situation you may also want to do the same thing for the OnPostReload event. Now, the document will always have one selected value for the field and if anyone tries to deselect it or select multiple values the trigger will switch to the default selection. For my document, I also checked off the Always One Selected Value checkbox in the Listbox Presentation tab.

[Author's Note 1Mar2013
Based on a reader comment, here's a slightly different expression that will return the selection to the previous valid selection instead of a default value:

=if(GetSelectedCount(CURRENCY)=1,UOM_SELECTION,if(isnull(Only({$1}CURRENCY)),'Euro',Only({$1}CURRENCY)))

It uses set analysis syntax to get the previous selection and only uses the default if the previous selection is null. 

Execute Oracle Procedure From The Loadscript

I worked on a project a few months ago where several Oracle database tables had been designed specifically to support a QlikView document. The tables contained summarized data extracted from a large database. Often, database software, running on a large server can process large amounts of data more efficiently than QlikView can do it within the loadscript. In this particular case, the solution would only work correctly if the tables were updated shortly before loading the QlikView document. I decided to try something different and execute an Oracle stored procedure from a loadscript command. The procedure would update the tables and then the remainder of the loadscript could load data from the tables with SQL select statements.

The syntax for the procedure execution took a little trial and error. I put the execution immediately after the ODBC connect statement. This procedure takes a parameter which is supplied from a document variable. The procedure, named update_rpt_data, is implemented within an Oracle package named corp_report. The loadscript statement looks like this:

// Call Oracle data summarization procedure
sql call corp_report.update_rpt_data('$(var_COUNTRY)');

In order to make this work correctly with Oracle, I had to add a COMMIT statement as the first executable line in the stored procedure. Without the commit, Oracle threw an error.

If you use this technique, remember that the procedure execution may take extra seconds or minutes and this will add to the time required for the loadscript. Other types of database software may require different syntax; this example applies only to Oracle. Note that the statement only executes the procedure – no data is being returned directly from the procedure.

★ ★ ★