topic Re: Bits Concept in QV in QlikView

Bits Concept in QV

abhaysingh — Thu, 24 Sep 2015 07:52:24 GMT

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

Re: Bits Concept in QV

alexandros17 — Thu, 24 Sep 2015 07:54:27 GMT

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

Re: Bits Concept in QV

abhaysingh — Thu, 24 Sep 2015 07:59:45 GMT

How i can Handle This...Any Suggestion?

Re: Bits Concept in QV

petter — Thu, 24 Sep 2015 08:01:36 GMT

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

Re: Bits Concept in QV

alexandros17 — Thu, 24 Sep 2015 08:03:26 GMT

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)

Re: Bits Concept in QV

tresB — Thu, 24 Sep 2015 08:04:47 GMT

SQLValue*(-1) as QVValue ?

Re: Bits Concept in QV

jonoogle — Thu, 17 Nov 2022 20:55:37 GMT

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.

Re: Bits Concept in QV

marcus_sommer — Fri, 18 Nov 2022 13:49:55 GMT

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

Re: Bits Concept in QV

jonoogle — Fri, 18 Nov 2022 17:39:11 GMT

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.