Dynamic Tables - An alternative approach to CONCAT using BIT operators

    This document assumes that the reader is aware of dynamic tables and the approach of using data islands to control the conditional showing/hiding of columns in a table. It also helps if you know a bit about binary.

     

    The standard approach for dynamic tables (a good example is seen in the 'What's New in QLikview 11' app.) uses 2 QV variables that calculate the concatenation of the selected dimensions and measures respectively. The conditions on the columns then use string INDEX functions to determine if they should be visible or not.

     

    I see a number of drawbacks to this approach:

    1. The variables are recalculated every time a selection is made.
    2. The variables can quickly contain long string values (e.g. 12 dimensions and 4 measures with titles averaging 12 characters would, when you include commas, create a variable (16 x 12) + (16-1) = 207 characters long = 3312bits)
    3. The conditional show logic uses an INDEX function to check the existence of a specific string in the concatenated list of selected fields. This is an expensive function, especially on long strings and has the inherent risk of lookup string appearing within another field name (e.g. EndDate & AppendDate). This is easy to avoid with small numbers of fields, but more onerous as the number of fields expands.


    My approach uses the BIT operators in QlikView to test a binary number (A) to see if it contains another binary number (B)


    Example:

    A = 1011 (i.e. 11 in decimal)

    B = 0100 (i.e. 8 in decimal)


    using the BITAND operator QlikView returns the value that is in A AND B. In this case the result would be 0.


    If B = 1000 then the BITAND expression would return 1000 as that is the value/s that is in A AND B.


    We can use this behaviour to create a binary lookup method for working out which fields should be shown in a Dynamic Table. A metaphor I like to use is a series of switches that control whether the field is on or off.

    pixar_lifted.jpg

    Image from Lifted animated-short by Pixar.


    Couldn't resist :-)


    To start with your setup tables have an extra column in them:


    _Dimensions:

    LOAD * INLINE [

    _DimensionValue, DimensionName

    1, WHO

    2, WHAT

    4, WHERE

    8, WHY

    16, HOW

    ];


    _Measures:

    LOAD * INLINE [

    _MeasureValue, MeasureName

    1, AMOUNT

    2, QUANTITY


    The result is a list of dimensions and measures that have a simple binary number (simple as in binary it would look a one followed by a set of zeroes. Anyone who deals with file sizes should be in familiar territory here. The numbers can get quite big in decimal (e.g. the 20th dimensions would have a _DimensionValue of 524288, but it (and all the proceeding dimensions fit into only 20 bits (the calculation of bits used is a bit more complicated than that, but this will be sufficient for this document.)

     

    If you are unsure about simple binary numbers then use this formula in Excel to give you a list of the numbers: =2 ^ (ROW()-1)


    So at this point you will have a list of dimension and measures all with simple binary codes associated to them...

    Now go ahead and put into your dashboard the 2 listboxes and dynamic table including all the fields you need.

    To control the visibility of each dimension field you will need the following expression:

     

    =IF(GetSelectedCount(_DimensionName)=0,0,

         IF(SUM(_DimensionValue) BITAND [insert DimensionValue of this field] > 0, 1, 0))

     

    This formula first deals with the possibility of no selections and then sums up the values of all of the selected dimensions and then tests to see if it contains the binary value of the field and returns that value. If it is not found then the expression will return a zero.

     

    The same applies to Measures:

    =IF(GetSelectedCount(_MeasureName)=0,0,

         IF(SUM(_MeasureValue) BITAND [insert MeasureValue of this field] > 0, 1, 0))


    Hopefully you can see the advantages of this approach in the fact that

    1. It avoids the need to create dynamic variables
    2. It avoids potentially horrendously long string variables
    3. It uses scarily-fast binary logic to determine visibility
    4. As long as you stick to simple binary numbers for the values there is no risk of misinterpreting a value.


    The attached QVW provides a demonstration of this approach in action.