skip to main |
skip to sidebar
Keeping with the toolkit theme, here's a macro that can copy a cycle group from one QlikView document to another. If you've done much development, you know that it is easy to copy charts and other sheet objects from document to document; but cycle groups cannot be copied and some of them represent a lot of work to completely replicate in another document.
Copy this macro code into the module area of your toolkit document and create a button to execute it. When the macro is executed it will ask for the pathname to the source document containing the cycle group to be copied. Then it will ask for the cycle group name (remember that upper and lower case is important), and then the pathname to the document that the cycle group will be copied into. If either document requires a username or password QlikView will ask for those as the macro opens the documents. As always, make copies of your documents before testing a macro like this.
Here's the macro code you can copy and paste:
Sub Copy_Cycle_Group_Doc_to_Doc
'------------------------------
' /* Copy Cycle Group From One QV Document to Another */
' /* QlikView Maven, March 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the Cycle Group you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
exit sub
end if
CGName=inputbox("Enter name of Cycle Group to copy:","Enter Cycle Group Name")
if trim(CGName)="" then 'no entry or cancel
exit sub
end if
fn_dest=inputbox("Enter pathname to destination document where the Cycle Group is to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
exit sub
end if
Set objSource=Application.OpenDoc(fn_source)
Set objDest=Application.OpenDoc(fn_dest)
Set SrcGrp = objSource.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = objDest.GetGroup(CGName)
If NewGrp Is Nothing Then 'check if cycle group exists in dest document
Set NewGrp = objDest.CreateGroup(CGName) 'doesn't exist so create it
Else
Set NewProps = NewGrp.GetProperties 'does exist so remove old definition
for i = 1 to NewProps.FieldDefs.Count
NewGrp.RemoveField 0
Next
End If
Set NewProps = NewGrp.GetProperties
'copy elements of cycle group from source to dest
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
'we're done, show a message and close down
x=msgbox(CGName&" copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Cycle_Group_Doc_to_Doc")
if x=vbOK then
ObjDest.Save
End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open for examination
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set SrcGrp=nothing
set SrcProps=nothing
set NewGrp=nothing
set NewProps=nothing
End Sub 'end of Copy_Cycle_Group_Doc_to_Doc
★★★
Here's an alternate version of that macro from last time. This macro can be copied into the module area of a QlikView document (I call mine tookit.qvw) and, when executed, it will copy variables from an external document to another external document. I find this one handier for the kind of work I do.
This document will ask you for the pathname to a source QlikView document containing the variables you want to copy. Then it will ask for the pathname to a destination document that will have the copied variables inserted. If either of the documents requires a password you will be asked for the password as the macro opens the documents. There's no error checking but the most common error for this macro is entering an invalid pathname. After the variables are copied, the macro will ask if it is OK to save the destination document. Remember, as always, to make a backup copy of your documents before testing macros like this.
Here's the code that you can copy and paste into your own toolkit document:
Sub Copy_Variables_Doc_to_Doc
'----------------------------
' /* Copy Variables From One QV Document to Another */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the variables you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
exit sub
end if
Set objSource=Application.OpenDoc(fn_source)
fn_dest=inputbox("Enter pathname to destination document where variables are to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
exit sub
end if
Set objDest=Application.OpenDoc(fn_dest)
'Loop through the variables
set objVars=objSource.GetVariableDescriptions
varctr=0
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 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
'we're done, show a message and close down
x=msgbox(varctr&" variables copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Variables_Doc_to_Doc")
if x=vbOK then
ObjDest.Save
End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub 'end of Copy_Variables_Doc_to_Doc
★★★
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
★★★
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").
★★★
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.
★ ★ ★
