Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP

Dynamic Tables - An alternative approach to CONCAT using BIT operators

Valued Contributor

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.

Attachments
Comments
Partner
Partner

Awesome - thank you.  I will give this a try!

Valued Contributor

You are welcome. I have been desperately trying to find time to write this up for months. It is a bit rushed, but you should get the idea.

0 Likes
Valued Contributor III

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).

Or in other situations the field did not appear

Thanks

This caused me a large amount of time to overcome this issue

MVP & Luminary
MVP & Luminary

Cool post. I like the idea of binary flags, but fear they may be a bit confusing to some. I agree that the "What's New.." example has some shortcomings.

I prefer not to use a separate field because then I have to keep them mapped. Here's the conditional expression I prefer:

=GetSelectedCount(_DimensionName) AND max(match(_DimensionName,'Country'))

Valued Contributor

Thanks Rob

The advantage of using a separate field key is that you can change the dimension name without having to update the dynamic table. If you see my example it also makes using treeview field names simpler.

0 Likes
Luminary
Luminary

Can be very useful, we (Deepak Vadithala ) used a similar approach in a recent application.

Actually using a combination of Bit flags within the code and the concat function. This way we could calculate combinations using BITAND & BITOR

=Concat( DISTINCT '[%'& Measure_Field_Name&']', ' BITOR ')

This allowed the user to change the scope of the calculation by adding or removing selection in a Data Island (Measure_Field_Name)

Something which couldn't be done by hardcoding due to the number of possible combinations

Contributor

This concat alternative for dynamic tables is one I use frequently. It makes users very very happy... so big thanks to DFoster9 for this post, which is 5 years old I know, but still - its awesome. For the first time though, I am running into a problem when referring to a large number of the Dimension Value, highlighted below. 

2019-08-06_16-14-19.png

I am using the same method of assigning the value by using 2 ^ n starting with 0. That works fine until I reach 62... 2^ 61 is okay, 2^62 isn't. I have read a lot about the bitand function, including Arturo Muñoz's very helpful post here, I suspect my problem is not a Qlik thing, but maybe I could change how I refer to the number. 

2019-08-06_16-28-02.png

the vGetAdhocFields variable is just the GetSelected Count. 

2019-08-06_16-30-40.png

I know that this sounds like a lot of dimensions but these really are valid in the business, and just a fraction of the possible fields. I would appreciate any suggestions anyone may have. 

Thank you

 

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-04-28 07:25 AM
Updated by: