If you follow this blog then you know that during the last weeks we’ve been working in our Choose your champion app for the FIFA World Cup tournament happening in Russia.

 

Our data provider, Gracenote, populates and periodically updates several tables in a SQL database with all the tournament data.

 

As an example, just assume the Players table looks like this:

 

Player

Actions

Player A

145

Player B

17

Player C

139

 

The field Actions contain “compressed” information for 8 different actions during a game. The value 145 encapsulates all the information about Player A's performance during a certain game.

 

Combining multiple values into a single field

 

It is a frequent practice to combine multiple flag or binary columns into a single decimal column, instead of creating multiple columns to store these indicators.

 

One of the main advantages of this system versus the alternative of creating a column for each one of the actions is that if later we decide to record a new type of action we can just add it to the Actions column versus having to create a new column for each field.

 

But, how can I read what Actions column content actually means?

 

To do so we need more information. We need to know how that column is being created. For example, let’s assume we have this description of Actions column:

 

Actions Legend

game played = 1

goal scored = 2

penalty scored = 4

penalty missed = 8

1st yellow card = 16

2nd yellow card = 32

red card = 64

assists = 128

 

The above's data is the key to understand what’s stored in the Action column. To get a better view of it, let's convert the base 10 integer “Value”, to binary as in the expanded representation table below:

 

 

Player A (145)

Player B (17)

Player C (139)

Game played (1)

1

1

1

Goal scored (2)

0

0

1

Penalty scored (4)

0

0

0

Penalty missed (8)

0

0

1

1st yellow card (16)

1

1

0

2nd yellow card (32)

0

0

0

Red card (64)

0

0

0

Assist (128)

1

0

1

 

Binary flags representation:

 

Player

Binary

Decimal

Player A

10010001

145

Player B

00010001

17

Player C

10001011

139

 

Description:

 

Player A: played the game, got a yellow card and gave an assist to other player to score a goal.

Player B: played the game and got a yellow.

Player C: played the game, scored a goal, missed a penalty and assisted for a goal.

 

How to find what we are looking for?

 

To do so, it’s important to familiarize with the available bitwise or bit operators in Qlik:

 

Bitnot, bitand, bitor, bitxor , >>, <<

 

For more examples and further explanation you can check this page: https://en.wikipedia.org/wiki/Bitwise_operation

 

Continuing with the example, let’s try to find players that have scored a goal.

 

We need to check if the second bit is set, that means 00000010 (or integer 2 in base 10)

 

Player

Value

Value bitand 2

Player A

145

0

Player B

17

0

Player C

139

2

 

Select
  Player,
  Value
From PlayerTable where Value bitand 2 >0;









 

That statement is selecting from PlayerTable where Value bitand 2 > 0. But, let's go row by row to see what's happening under the hood:

A bitwise AND takes two equal-length binary representations and performs the logical AND operation on each pair of the corresponding bits, by multiplying them. Thus, if both bits in the compared position are 1, the bit in the resulting binary representation is 1 (1 × 1 = 1); otherwise, the result is 0 (1 × 0 = 0 and 0 × 0 = 0)

 

Player A

        10010001 (145 decimal)

bitand  00000010 (2 decimal)

        ---------

returns 00000000 (0 decimal)

 

Player B

        00010001 (17 decimal)

bitand  00000010 (2 decimal)

        ---------

returns 00000000 (0 decimal)

 

Player C

        10001011 (139 decimal)

bitand  00000010 (2 decimal)

        ---------

returns 00000010 (2 decimal)

 

 

Find the players who received a “Yellow Card” and did not “Assist”


We need to check if the fifth bit is set,  00010000 (or integer 16 in base 10) and if the eight bit is not set (128 base 10)


Player

Value

Value bitand 16

Value bitand 128

Player A

145

16

128

Player B

17

16

0

Player C

139

0

128

 

Select
  Player,
  Value
From PlayerTable where Value bitand 16 > 0 and Value bitand 128 = 0;









 

 

Bit operators in expressions

 

Remember you can use the bit operators to create expressions too, so in a object we could do:

 

=count(distinct {<Value = {"=Value bitand 16 > 0"}>} Player)





 

This counts how many players have seen a yellow card so far, note how we are comparing the Value with the flag yellow card (16).

 

As you can see in the examples, the use of the bitand operator has allowed us to compare two different values at a binary level and returns whether the two numbers intersect allowing me to select and/or count the correct numbers.

 

Hope you find this interesting.

Arturo (@arturoqv)

 

PS: I'm attaching a QlikView and Qlik Sense app with the example data so you can test it out yourselves.