topic Re: Bitwise / bitflag / flag column data in QlikView

Bitwise / bitflag / flag column data

Sun, 17 Aug 2014 09:36:00 GMT

Hello!

I have data which I have a problem understanding how to import/visualize in QlikView.

It follows: Bitwise operation - Wikipedia, the free encyclopedia

Colors:

LOAD * INLINE [

ColorName, CarColor

Red, 1

Yellow, 2

Green, 4

Blue, 8

Orange, 16

];

Cars:

LOAD * INLINE [

CarName, CarColor

A, 1

B, 2

C, 4

D, 8

E, 16

F, 3

G, 12

H, 7

];

I have two different usages and expected results:

CaseA (all is required):

Red -> A

Yellow -> B

Red+Yellow -> (1+2 = 3) -> F

Red+Yellow+Green - > (1+2+4 = 7) -> H

Case B (any required)

Red (1) -> A + F (1 + 2) + H (1 + 2+ 4)

Red+Yellow -> F(1 + 2) +H (1+2+4)

This is clearly not working right out of the box since Qlik matches the integers as they are, and not as the calculations I want done.

How do I solve this?

/Sebastian

Re: Bitwise / bitflag / flag column data

MarcoWedel — Sun, 17 Aug 2014 23:43:06 GMT

Hi Sebastian,

one solution for at least parts of your requirements:

Colors:
LOAD * INLINE [
    ColorName, CarColor
    Red, 1
    Yellow, 2
    Green, 4
    Blue, 8
    Orange, 16
];

Cars:
LOAD *
Where CarColor;
LOAD *,
    CarColors bitand pow(2, IterNo()-1) as CarColor
While IterNo() <= Floor(log(CarColors)/log(2))+1;
LOAD * INLINE [
    CarName, CarColors
    A, 1
    B, 2
    C, 4
    D, 8
    E, 16
    F, 3
    G, 12
    H, 7
];

hope this helps

regards

Marco

Re: Re: Bitwise / bitflag / flag column data

Tue, 19 Aug 2014 06:40:45 GMT

Hello Marco!

Thanks for your answer! I will test it more in depth to see how it matches my use-cases, but it is a nice push in the right direction!

Would it be correct to use the following code to get a "link table" instead? This would avoid getting duplicate "car"-entrys...

CarsColorsLinkTable:  
LOAD CarColor, CarName  
Where CarColor;  
LOAD *,  
    CarColors bitand pow(2, IterNo()-1) as CarColor  
While IterNo() <= Floor(log(CarColors)/log(2))+1;  
LOAD * Resident Cars;

Thnx!

Re: Bitwise / bitflag / flag column data

MarcoWedel — Tue, 19 Aug 2014 08:37:32 GMT

Looks good. But make sure the CarColor column is renamed to CarColors in your initial car table.

Regards

Marco