Bits Concept in QV

[abhaysingh]

Hi All,

I am extracting Data from SQL, In few table there are fields having Data Type In Bit and Field Value (1,0)

But When i am extracting Data in Qlikview than Field Value having Data Type in Bit, Getting Value (-1,0).

Can any one suggest..

Regards

Abhay

[alexandros17]

True value in Qlik is -1 not 1 as usual ...

[abhaysingh]

How i can Handle This...Any Suggestion?

[petter · Accepted solution]

The reason QlikView stores it as -1 and 0 is that it is able to make an efficient storage into bits because it use a symbol table. So the -1 and 0 as True() and False() values are more handy than 1 or 0.

Unfortunately due to some weird limitation in Set Expressions an expression with a set element like -1 will not work properly so if you intend to use this bit value as a flag you should either invert the value so it gets 1 and 0 also in QlikView or you need to single quote the value in a set element like this:

     Sum( { < IsPromotion={'-1'} Sales )

     alternatively you can do

     Sum( { < IsPromotion={1} Sales )

     but

     Sum( { IsPromotion={-1} Sales )    will NOT work...

In your load statement you could do:

     -(Field1) AS Field1

[alexandros17]

If you need to manage in qlik as a boolean value then leave it as is, otherwise add a new field (Integer) and write 1 instead of -1

(only a simple idea ... it depends on what you need)

[tresesco]

SQLValue*(-1) as QVValue ?

[jonoogle]

I just came across this  after having a similar problem after migrating from different versions of SQL.  I do not believe this is correct.

"The reason QlikView stores it as -1 and 0 is that it is able to make an efficient storage into bits because it use a symbol table. So the -1 and 0 as True() and False() values are more handy than 1 or 0."

It is dependent on the driver and not qlik. With MS SQL, I found that the SQL native client driver will load a bit field as 0/1 and the OLEDB driver will load it as 0/-1.

[marcus_sommer]

If Qlik stores true() / false() it stores as hinted -1 / 0. But within each calculating only the 0 is relevant because this is false() and each other value regardless if the value is -1 or 1 or even 'a' is true().

This means within many scenarios the real value behind true() isn't relevant. Personally I would ensure that each stored boolean true() value is 1 - maybe forced per fabs() or ven with fabs(sign()) - because I use them often as flag-values for multiplications or to sum them.

- Marcus

[jonoogle]

Hi Marcus,

thanks for your response. The original poster had the same issue I was running into; it was not a true/false issue. He was expecting a 1/0 for a bit field and instead was getting a -1/0. This actually is not a Qlik issue, rather the driver which interprets a bit field differently. 

Personally I would not have used a bit field, but this was code I inherited and we ran across this in a migration. I think there are many different solutions, two of which you mentioned: if you can, use the "0" rather than the -1/1,  2) use fabs function, 3) avoid bit fields.