topic SQL bit type field resulting in 0, -1 instead of 0, 1 in QlikView

SQL bit type field resulting in 0, -1 instead of 0, 1

Wed, 14 Oct 2009 02:02:37 GMT

I have a table in a SQL db with a bit field. When I select distinct values in SQL management studio, I get 0 and 1, but in QlikView I get 0 and -1. Is there some special way I'm supposed to handle bit types? Thanks!

SQL bit type field resulting in 0, -1 instead of 0, 1

Oleg_Troyansky — Wed, 14 Oct 2009 02:28:30 GMT

Sounds similar to boolean fields... QlikView treats boolean "true" as -1.

If you are consistently getting -1, then just reverse the sign during load.

SQL bit type field resulting in 0, -1 instead of 0, 1

biester — Wed, 14 Oct 2009 03:17:16 GMT

I agree with Oleg that sign should be reversed (or by using an absolute value be omitted), but would use (assuming that the name of the bit field is BitField):

fabs(BitField Xor 0)

Thus, if you expect only 0 or 1, you only GET 0 or 1 (even if BitField is not consistently read as -1).

But that's perhaps mere of theoretical interest ..

Rgds,
Joachim

Re: SQL bit type field resulting in 0, -1 instead of 0, 1

Anonymous — Wed, 10 Jan 2018 16:22:03 GMT

Hi, I am new to QlikView, and had the same unexpected experience using BIT values from SQL Server.

A work-around I used was to change the SQL load script and convert data type to INT, e.g.

,cast( t.IsActive as INT) as IsActive

Hope this helps.

Regards

Terry