May 13, 2012

How to write reusable and expandable expressions in QlikView

One of the good practices in QlikView development is using reusable expressions. The most common case is when a repeating expression is replaced with dollar-sign expansion through the application. E.g.

$(eAmount)

where eAmount is defined like, for instance

LET eAmount = 'sum(Amount)';

If you don't use reusable expressions in QlikView you definitely should start doing it because of 2 reasons:
  1. Improved maintainability -- you don't have to edit all occurrences of an expression in an application, but only once
  2. Better readability -- you have much better readability of your expressions because their (sometimes complex) logic is actually replaced with its brief explanation.

While this a generally good practice there are a couple of tricks to get even more value from it.



One trick is to use expansions inside set analysis definitions. In this case we can achieve some sort of flexibility by combining search conditions. For example:

Definition
LET sPrevYear = 'Year={"=only(Year)-1"}';
LET sHeadoffice = 'CostCenter={"Head Office"}';

Examples of use
sum( {< $(sPrevYear)>} Amount)
sum( {< $(sPrevYear), $(sHeadoffice) >} Amount)
count( {< $(sHeadoffice) >} Employee)

However we can go even beyond that, and use expansions with parameters inside set definitions. This allows us to achieve 2 goals:
  1. Reduce number of reusable expressions
  2. Cover cases with expressions which occur only once and/or has set definition that only slightly differs from an already defined reusable expression
Examples:
    Definition
    LET ePrevAmount = sum ( {<Year={"=only(Year)-1"}, $1 >} Amount)

    Example of use
    $(ePrevAmount( Type={Income} )) - $(ePrevAmount( Type={Expense} )) + $(ePrevAmount( Account={91000} ))

    The trick is that you can still use such expansion without parameters, so

    $(ePrevAmount)

    works just fine, because when expanded it turns into

    sum ( {<Year={"=only(Year)-1"}, $1 >} Amount)

    where $1 appears for QlikView like an absolutely valid naming, which just means nothing specific in this case, so QlikView simply ignores it without raising an error. So, we're getting very flexible reusable expressions.

    This approach has one downside that is that QlikView doesn't allow passing strings with commas as expansion parameters because everything after comma is considered as next parameter. Therefore something like

    $(ePrevAmount( City={NY}, CostCenter={Headoffice}))

    or

    $(ePrevAmount( City={NY, Chicago, Boston}))

    simply won't work. Adding more parameters for expansion won't help a lot as QlikView's scripting language doesn't support function overloading.

    Fortunately, there is a bit dirty but still efficient workaround for this limitation. It is based on the fact that conditions in a set definition actually have boolean operation AND applied between them. For instance set definition

    {<Year={2012}, City={NY}, Product={Loan}>}

    actually means

    Year=2012 AND City=NY AND Product=Loan

    However, we can achieve the same logic by using intersection of sets. Therefore we can rewrite the example above as follows:

    {<Year={2012}> * <City={NY}> * <Product={Loan}> }

    In this case we still can define sets with several conditions, but avoid commas. Therefore, we can use  this syntax in our reusable expressions -- we simply replace commas with >*<, which doesn't look very good, but works fine.

    Example of use
    $(ePrevAmount( Type={Income} >*< Aggregated={Yes} >*< CostCenter={NY} ))

    We also can use the same technique to avoid commas in search lists. But in this case we need to model boolean operator OR, so we need to use union of sets instead of intersection:

    $(ePrevAmount( City={NY} >+< City={Boston} >+< City={Chicago} ))

    Again, this is not very aesthetically pleasing, but in some case this is still better than complex poorly readable and non-reusable expressions.

    PS. It should be noted that combination of intersection and union should be done carefully with respect to operation priority. Otherwise it might lead to unexpected results.

    UPDATE

    Anatoly (sparur) has offered more simple and elegant solution for avoiding commas in his blog (see link in comment below). For those who can't read russian, he suggests to use any other symbol for dividing values (e.g. semicolon) and then replacing it with comma dynamically right before using in expression. For example, instead of

    LET ePrevAmount = sum ( {<year={"=only(year)-1"}, $1="">} Amount)

    one can write

    LET ePrevAmount = sum ( {<Year={"=only(Year)-1"}, $(=Replace('$1', ';', ',')) >} Amount)

    and then use expressions like

    $(ePrevAmount( Type={Income}; Aggregated={Yes}; CostCenter={NY} ))

    or

    $(ePrevAmount( City={'NY';'Boston';'Chicago'} ))

    Much better, isn't?