Bitwise / bitflag / flag column data

[#1]

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

[MarcoWedel]

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

[#3]

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!

[MarcoWedel]

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

Regards

Marco