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
        ' 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

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 + ")"
            vString = v.getContent.String
            endPos = 0
        end if
        expression = before + vString + after
        startPos = InStr(endPos+1, expression, "$(")
    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

This entry was posted in QlikView and tagged , , , , . Bookmark the permalink.

10 Responses to QlikView VB Scripting For Newbies

  1. Rafih says:

    I have been reading your articles and I love all of them. I’ve some questions I need answers to and this is probably the 100th comment I am leaving but haven’t had any decent answers. I am sure you have the answers to my questions.
    1. Is the sample script above visual basic script or normal visual basic?
    2. Do you enter the code in the Edit Module in QlikView or is it coded outside QlikView. If it is coded out side QlikView, where did you get the OCX library from? the link referenced only has QVX library.

    • amethyst23s says:

      Hi Rafih! I’m so glad my posts have been helpful to someone!

      1. I honestly don’t know the technical difference between a visual basic script and normal visual basic… my guess would be that this is just visual basic, but I used whichever language QlikView supports.

      2. This code was written in the Edit Module script. To run it from a QlikView document, you can add an action to a button with the type “Run Macro” and the sub name as its input.

      I don’t claim to be an expert in visual basic or QlikView scripting, but I have been playing around with it a fair amount in the last few months. If you have any other questions, feel free to ask!

      • Rafih says:

        Hi amethyst23s,
        Thanks for the reply, I really appreciate you taking the to answer my questions. I read some where that with macros you are limited to do certain things in QlikView for example like create objects such as charts, pie charts, straight tables and pivot tables, filled with pre-set dimensions and expressions in one go. From your experience, do you think that it is possible to use macros to create an object or multiple objects with set dimension and expressions?


      • amethyst23s says:

        Sorry for the delay in response. I know you can create blank charts and add or remove dimensions and expressions through a macro. (If you look at the code above, you can see the RemoveExpression and AddExpression as examples of how to do this). If you have static dimensions and expressions that you want to set, you should definitely be able to do this.

  2. gkastros says:

    Totally agree with you about the lack of help and support for qlikview. Worked with Ms SSAS before and the help resources where unlimited. Wonderfull post, thanks a lot.

  3. Tina says:

    Apparently I can’t open this documentation because I have the free version. Since that’s what you use, how did you open it?

    • amethyst23s says:

      I don’t know — I didn’t have that problem. This was nearly a year ago so I may have forgotten some details (maybe it used one recovery attempt?), but I think I was able to open it without hassle. Maybe you find a better link to the document somewhere else.

  4. Bobby says:

    The document (APIGuide) doesn’t open in QV11, can you please post the important aspects of it? Would really aprreciate it.

    • amethyst23s says:

      Hi Bobby – I wish I could, but I actually don’t have it anymore. I have changed computers since I originally wrote this post and haven’t needed to use QlikView since then. Maybe they have a new guide somewhere for the new version?

  5. redpeas says:

    Reblogged this on Nate's XBRL Blog and commented:
    I like documentation — thanks for finding the “golden egg” of Qlikview VBA API, denvercoder9

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s