topic Re: OleDB loaded boolean value as -1 in QlikView

OleDB loaded boolean value as -1

Fri, 10 Apr 2015 08:14:45 GMT

Our existing Qlikview app uses ODBC load SQL database data. Recently we decided to change the provider to OleDB. we noticed that the Boolean value "True" is presented as -1 instead of 1 as in SQL server. Is there a way to make the true value show as 1? Thanks for your help.

Re: OleDB loaded boolean value as -1

Ralf-Narfeldt — Fri, 10 Apr 2015 10:54:28 GMT

TRUE is always presented as -1 in QlikView as opposed to in SQL where it's 1.

But you can change sign to 1 with the function fabs(). When QlikView makes a boolean test, any non-zero value will still evaluate as TRUE.

Load *, If(convertedbool,'true', 'false') as test;

LOAD *, fabs(bool) as convertedbool;

LOAD * INLINE [

id, bool

1, -1

2, 0

];

As you can see 1 is evaluated as true

Please mark as Correct answer if you are happy with the reply

Re: OleDB loaded boolean value as -1

Fri, 10 Apr 2015 11:45:33 GMT

Thanks for your answer,Ralf. My focus was slightly different. I should explain the process better.

I have a table in sql database with boolean field. the true value there is presented as 1.

I use ODBC connection load the table to Qlikview. the true value still shows as 1.

then I changed connection string to OleDB, reload the loaded table, the true value show as -1.

I was wondering if there are ways to change the representation while loading with OleDB.

Re: OleDB loaded boolean value as -1

Ralf-Narfeldt — Fri, 10 Apr 2015 11:59:06 GMT

If it shows as 1 when you load with ODBC, is it really interpreted correctly as a boolean?

I don't see a way to change the representation while loading with OLE DB, but you can do it in QlikView after it's loaded from the DB as I showed you if it is necessary.

Re: OleDB loaded boolean value as -1

jonathandienst — Fri, 10 Apr 2015 12:01:42 GMT

I suspect it is due to differences in the way the boolean type is handled by ODBC and OLEDB. ODBC treats the boolean as a bit type, which has possible values of 0 and 1.

Perhaps OLEDB treats it as a Boolean type, and that is then converted to a 16 or 32 bit bit integer with all the bits set to 1 for true or 0 for false - this results in a value of -1 for true. And no, I don't think you can change that without casting the boolean to another type in your SQL query.

It usually doesn't matter - in Qlikview, 1 and -1 are both treated as true...

Fabs(...) will convert both to +1, if it matters in your case.

HTH

Jonathan

Re: OleDB loaded boolean value as -1

Fri, 10 Apr 2015 14:13:22 GMT

yes, it is not a real issue so to speak. thanks for the clarification.