Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
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
yes, it is not a real issue so to speak. thanks for the clarification.