Qlikview Interpreting "1" as "-1"

[#1]

Hi,

I am loading some data from sql into a QVD.

For the Active column its showing : 1

In the QVD data is stored as -1.

Please help

[hic]

Booleans in a DB are stored as -1 for TRUE and 0 for FALSE. This is what QlikView shows. Your DB viewer makes an implicit conversion of TRUE to 1, so it shows the wrong number, in fact.

I suggest you use

     If(Active=0, Dual('FALSE',0), Dual('TRUE,-1))

when you load it.

HIC

[Jason_Michaelides]

The answer to "why" is that QlikView is interpreting the ACTIVE column as a boolean field (as it should). However QlikView's boolean values are -1 (TRUE) and 0 (FALSE), not 1 and zero as you intend.

EDIT: somehow when posting this I didn't see the last three posts which already answered you! Sorry, all.

[gerhard_jakubec]

Thank you Henric,

i confirm this behaviour. Using:

// connect to db

LET vODBCDataSource = 'mySysDsnName';

ODBC CONNECT TO $(vODBCDataSource);

// watch out when you limit data within load statemens based upon boolean / bit fields

myTable:

LOAD  *

  // where import_flag = 1 // will NOT work as desired

;

SQL

  SELECT * FROM [mySchema].[myTable]

  WHERE import_flag = 1 -- should work as desired

;

[#5]

Hi,

Table1:

Load *,

        Fabs(Active) as Active1

from Table1;

rename field Active1 as Active;

[hic]

I would not completely trust a where clause like

     WHERE import_flag = 1

I think it is better to use

     WHERE import_flag <> 0

which will work also in the QlikView where clause.

HIC