Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikview Interpreting "1" as "-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

Issue2.jpg

Issue1.jpg

14 Replies

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

Luminary
Luminary

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.

Contributor III
Contributor III

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

;

Not applicable

Hi,

Table1:

Load *,

        Fabs(Active) as Active1

from Table1;

rename field Active1 as Active;

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