20 Comments

  • 1
    September 21, 2011 - 17:44 | Permalink

    Will this method work if the expression contains a reference to another variable? For example, we allow the user to select their unit of measure, such as Cases or Pounds, and store their selection in a variable. Then the expressions use that variable in calculations. So we end up with:

    sum({$}Price*$(vMeasure))

    I haven’t been able to load this type of expression from an external file because QlikView wants to interpret the vMeasure variable as it’s loaded.

    • 2
      September 21, 2011 - 21:49 | Permalink

      It should. I had the same problem with Dollar-sign expansions getting interpreted by the script. The solution was not to use an intermediate variable to store the expression code in the script, instead I peek it straight into the variable.

      • 3
        Pablo Labbe
        October 31, 2011 - 13:01 | Permalink

        Barry, Interesting solution. Can you post a sample code to exemplify your solution to avoid the interpretation in the load script ?

        • 4
          November 2, 2011 - 22:34 | Permalink

          Hi Pablo,

          The example QVW contains examples of Dollar-sign expansion in the variables that does not get interpreted by the script, but is written straight to the variable definition. Drop me an email via the contact form if you want to know more.

          Cheers,

          Barry

  • 5
    September 21, 2011 - 17:46 | Permalink

    That expression got altered when I posted but I think you can see what I mean.

  • 6
    September 21, 2011 - 22:10 | Permalink

    Yep, seems to work. Thanks!

  • 7
    September 22, 2011 - 01:04 | Permalink

    Nice solution, and one I use myself. It can get a bit more involved where you have expressions that need single quotes in them. I have in the past switched these out with a substitute in Excel (to a ¬ or somesuch) and then replace them back with a char(39) in QlikView.

    A further step is to have a table of expressions loaded in QlikView that cant then be selected – so you can have a single chart for which you can pick the expression.

    • 8
      September 22, 2011 - 10:07 | Permalink

      Agree about the single quotes, those can be a real hassle. QlikView could use an unescape function.

      I’ve used the table of expressions to create a cycle group for expressions as well. Might be a nice subject for a short blog sometime.

  • 9
    September 23, 2011 - 09:20 | Permalink

    Hi Barry,

    Great solution! Do you know a way to sync the variables between the excel and the qvw’s > if I remove a variable in the excel file it will continue to exist in the qvw isn’t it?

    best regards
    William

    • 10
      September 23, 2011 - 20:13 | Permalink

      Hi William,

      You could write a macro that checks the existing variables to the ones that are being loaded and removes everything that does not belong. Not a pretty solution though, especially if you have application specific variables or variables that are being generated as part of your time dimension. Something to think about ;)

      Cheers,
      Barry

  • 11
    September 23, 2011 - 18:06 | Permalink

    Nice know-how! I have been looking for some thing similar to this for some time today. With thanks!

  • 12
    October 8, 2011 - 14:18 | Permalink

    Hi Barry,

    This is a great tip! Always found it a big hassle to administrate and keep track of all variables in the QlikView interface

    Thanks!

    Renco

  • 13
    Chris Cammers
    October 8, 2011 - 14:44 | Permalink

    If you don’t want to be quite so fancy you could just use an include file.

    I think the key advantage is the ability to store the formulas AND DESCRIPTIONS in a table and then present them in a list box on your applications.

  • 14
    October 18, 2011 - 22:18 | Permalink

    Great post Barry!

    One convention I try to follow when using this technique is to wrap the entire expression (in the Excel cell) with parentheses. I do this to help manage operator precedence. Example:

    sum(Price * Amount) –> ( sum(Price * Amount) )

    That first example was trivial, but you can see more relevance in this example:

    ( sum(Price * Amount) / sum(NbrOfUnits) )

    So now if this variable is used in the denominator of an expression, the “sub” denominator sum(NbrOfUnits) will be correctly handled.

    Cheers,
    Bill

    • 15
      October 19, 2011 - 07:17 | Permalink

      Good tip! I’ll add it to my coding conventions.

  • 16
    Francois
    March 13, 2012 - 17:54 | Permalink

    Awesome blog and post barry!

    Very curious about the Daft Punk image on the post, tried to find the association but maybe it’s just because you’re a fan? :)

    • 17
      March 13, 2012 - 18:08 | Permalink

      Hi Francois,

      Thanks.

      Regarding the Daft Punk image, I definitely enjoy Daft Punk* but the association is probably a bit too far-fetched. This was my third post in a row about QlikView variables, I thought the subject was getting a bit monotonous but figured: One more time…..

      Cheers,
      Barry

      * My interest in Daft Punk was recently rekindled by these funky 8 bit mixes: http://dachip.com/

  • 18
    Johnqv
    August 19, 2013 - 08:59 | Permalink

    Can anyone please help me in understanding the code which is used to writeback the data from qlikview to the database.I do not have any idea on vbscript or javascript.

    It would be a great help

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

    Read previous post:
    Testing the performance implications of variables and label referencing versus direct expressions

    After my previous post about variables, I got an interesting question from DV. He asked me about the reuse of...

    Close