QlikView VB Scripting For Newbies

I’ve been working with QlikView for a few weeks now, and I’ve finally run into the need for a script. (yay!) I’ve never used Visual Basic before, and have no familiarity with the QlikView API, but I’m pretty decent at picking up new languages. How hard could this possibly be?

… 3 hours later …

QLIKVIEW, WHAT HAPPENED TO YOUR DOCUMENTATION?!!!!! Am I supposed to magically know what all these strange Document and Sheet objects are and what functions they contain? Why does every google query pass me off to your help forums THAT DON’T ACTUALLY HAVE ANY HELP! Okay, maybe they would be helpful if I could open the sample code people keep attaching to their cryptic forum replies. But, oh wait — I can’t. Because I have the free version, and these people actually paid for theirs. So basically, no help for the newbies around here. Gotta work through it on my own.

After a bit more creative googling, I finally found the golden egg.
——————–> REAL DOCUMENTATION!! <——————–
And you even let me open it in my free version! Thank you, Qlikview, for being so kind. This dynamic chart is a bit frustrating to use, but at this point I'll take what I can get.

In case you are like I was 4 hours ago, having no idea how to write a VB script and are fed up with the fact that you can't open anyone's sample documents, I'm including some code I wrote below. Yes, it has a very specific purpose that you probably don't care much about, but maybe you can at least pull some syntax-y things out of it. You might want to see what I did in my first QlikView post to get an idea of what all these sorting variables are about.


' For every graph on the same active page, freezes its attributes
' to no longer respond to change in sorting variable
sub freezeGraph
    ' Find the current value of the sorting variable
    set currDim = ActiveDocument.Variables("v_sortSelected")
    ' Find the graphs on this page
    graphs = ActiveDocument.Activesheet.GetGraphs
    for i = lbound(graphs) to ubound(graphs)

        ' Set properties
        properties = graphs(i).getProperties.ChartProperties

        ' Title
        title = properties.Title.Title.v
        title = replaceVariable(title)
        properties.Title.Title.v = title

        graphs(i).setProperties properties

        ' Set dimensions and expressions

        ' Find any dimension dependent on this variable, and replace
        ' it with the value
        set dims = graphs(i).getProperties.Dimensions
        for j = 0 to dims.Count - 1
            newName = dims(j).PseudoDef.Name
            newName = replaceVariable(newName)
            if not(StrComp(dims(j).PseudoDef.Name, newName) = 0) then
                graphs(i).RemoveDimension j
                ' Clean expression
                newName = Replace(newName, "=", "")
                newName = Replace(newName, """", "")
                graphs(i).AddDimension newName
            end if
        next
        ' Do the same for expressions
        set exprs = graphs(i).getProperties.Expressions
        for j = 0 to exprs.Count - 1
            oldName = exprs.Item(j).Item(0).Data.ExpressionData.Definition.v
            newName = replaceVariable(oldName)
            if not(StrComp(oldName, newName) = 0) then
                graphs(i).RemoveExpression j
                ' Clean expression
                newName = Replace(newName, "=", "")
                newName = Replace(newName, """", "")
                graphs(i).AddExpression newName
            end if
        next

    next
end sub

' Replaces a variable (denoted by $(v_name)) with its actual value
Function replaceVariable(expression)
    ' Find the occurrance of the variable
    startPos = InStr(1, expression, "$(")
    OFFSET = 2
    Do While not(startPos = 0)
        ' Split string
        endPos = InStr(startPos + OFFSET, expression, ")")
        ' Mid(string, startPos, length)
        before = Mid(expression, 1, startPos-1)
        var = Mid(expression, startPos + OFFSET, endPos - startPos - OFFSET)
        after = Mid(expression, endPos + 1, Len(expression))

        ' Replace variable with value
        set v = ActiveDocument.Variables(var)
        if (v is Nothing) then ' If the variable doesn't exist, put it back
            vString = "$(" + var + ")"
        else
            vString = v.getContent.String
            endPos = 0
        end if
        expression = before + vString + after
        startPos = InStr(endPos+1, expression, "$(")
    Loop
    replaceVariable = expression
End Function


For the sake of Google-Searchability, lets summarize what techniques you might get out of this code: QlikView Variables, Change Dimensions, Change Expressions, Change Chart Title