Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Macro functionality lost between QV 8.5 and 10

In the process of upgrading from 8.5 to 10. An existing macro (in 8.5) which rebuilds a pivot table by adding/removing dimensions and enabling/disabling expressions, based on two list boxes populated by an inline load, has worked fine. Now, in 10, the macro fails, resulting in one of two errors; 1) Allocated memory exceeded, and 2) the pivot table grayed out with the diagonal lines stretching from corner to corner. Upon checking the properties of the pivot table after the macro has run (activated by a button via Run Macro), the dimensions and expressions both exhibit the correct characteristics as modified by the macro, and then, without altering anything, upon selecting OK in the properties dialogue box, the pivot table proceeds to refresh and display correctly. The macro is included below.

Any ideas as to why this might be? Maybe i need to refresh the chart in a new way?

Thanks for your help.

sub rebuild()
set vAllowCalc = ActiveDocument.Variables("vAllowCalc")
vAllowCalc.SetContent 0, true 'Display the "Please wait..." text object
ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report
set selectedExpressions = ActiveDocument.getField("Expr").getSelectedValues
set selectedDimensions = ActiveDocument.getField("DimField").getSelectedValues

CollapseCht

selectedExpressionMax = selectedExpressions.Count - 1
selectedDimensionMax = selectedDimensions.Count - 1
if selectedExpressionMax + selectedDimensionMax > -2 then 'bypass if nothing is selected
'--------------------------- TABLE BOX ONLY? ---------------------------------------------------
if selectedExpressionMax = -1 then
set table = ActiveDocument.getSheetObject("TB01")
tableDimensionMax = table.GetColumnCount - 1
for i = tableDimensionMax to 0 step -1 'remove existing dimensions
table.removeField(i)
next
for j = 0 to selectedDimensionMax 'add selected dimensions
table.addField(selectedDimensions.Item(j).Text)
next

else
'--------------------------- EXPRESSIONS ------------------------------------------------------
set chart = ActiveDocument.getSheetObject("CH642")
set chartProperties = chart.GetProperties
set chartExpressions = chartProperties.Expressions
chartExpressionMax = chartExpressions.Count - 1
dim selectedExpression(100) 'runs slightly faster if first set up a simple array
for j = 0 to selectedExpressionMax
selectedExpression(j) = selectedExpressions.Item(j).Text
next
for i = 0 to chartExpressionMax 'for every possible expression defined to the chart
chartExpression = chartExpressions.Item(i).Item(0).Data.ExpressionVisual.Label.v
expression = chartExpressions.Item(i).Item(0).Data.ExpressionData
expression.Enable = false 'disable the expression
for j = 0 to selectedExpressionMax 'check all selected expressions
if selectedExpression(j) = chartExpression then 'if user selected the expression
expression.Enable = true 'enable it
j = 9999 'and exit the inner loop
end if
next
next
ActiveDocument.GetApplication.WaitForIdle
chart.SetProperties chartProperties
'--------------------------- DIMENSIONS --------------------------------------------------------
set vDimCnt = ActiveDocument.Variables("vChtDimCnt")
vDimCnt2 = vDimCnt.GetContent.String
chartDimensionMax = vDimCnt2
for i = chartDimensionMax to 0 step -1 'remove existing dimensions
chart.removeDimension(i)
next
for j = 0 to selectedDimensionMax 'add selected dimensions
chart.addDimension(selectedDimensions.Item(j).Text)
next
end if
end if

ActiveDocument.Variables("vChtDimCnt").SetContent selectedDimensionMax,true
vAllowCalc.SetContent 1, true 'allow dynamic report to recalculate
ActiveDocument.Variables("vRefrPrompt").SetContent "0",true
end sub

6 Replies
Not applicable

Macro functionality lost between QV 8.5 and 10

One last shot here.... Any ideas?

Thanks

Not applicable

Macro functionality lost between QV 8.5 and 10

One last shot here... Any ideas?

Thanks

jp_golay
Contributor

Macro functionality lost between QV 8.5 and 10

I have same problem the addfield get the tablebox graxed with a cross.

Have you found a solution

Thanks

Not applicable

Macro functionality lost between QV 8.5 and 10

I called the Expand/Collapse function we already had coded, I just called them again as a part of the other macro at the end...

Sub ExpandCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = true
chart.SetProperties gp

End Sub

Sub CollapseCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = false
chart.SetProperties gp

End Sub

Does that make sense...?

Not applicable

Macro functionality lost between QV 8.5 and 10

Hi Rob,

Do you have resolution for this yet ? I have the same problem.

And adding the expand sub macro in the original macro still does not work.

Not applicable

Macro functionality lost between QV 8.5 and 10

Beyond the Expand/Collapse macro calls, I have no other solutions. The macros fixed my problems so I stopped troubleshooting. Sorry I can't be of more help.

Community Browser