Sign InHelp

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Documents for QlikView related information.

- Qlik Community
- :
- Qlik Data Analytics Forums
- :
- QlikView
- :
- QlikView Documents
- :
- Dynamic Tables - An alternative approach to CONCAT...

Options

- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

dfoster9

Valued Contributor

2014-04-28
07:25 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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:

- The variables are recalculated every time a selection is made.
- 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)
- 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.

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

- It avoids the need to create dynamic variables
- It avoids potentially horrendously long string variables
- It uses scarily-fast binary logic to determine visibility
- 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.

Comments

jason_michaelid

Partner

2014-04-29
08:19 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

dfoster9

Valued Contributor

2014-04-29
08:21 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

robert99

Valued Contributor III

2014-04-29
10:36 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

*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

rwunderlich

MVP & Luminary

2014-04-30
01:14 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

dfoster9

Valued Contributor

2014-04-30
04:11 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

richard_pearce6

Luminary

2014-04-30
07:07 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

denareavis

Contributor

2019-08-06
04:41 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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.

the vGetAdhocFields variable is just the GetSelected Count.

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

Version history