Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am extracting Data from SQL, In few table there are fields having Data Type In Bit and Field Value (1,0)
But When i am extracting Data in Qlikview than Field Value having Data Type in Bit, Getting Value (-1,0).
Can any one suggest..
Regards
Abhay
The reason QlikView stores it as -1 and 0 is that it is able to make an efficient storage into bits because it use a symbol table. So the -1 and 0 as True() and False() values are more handy than 1 or 0.
Unfortunately due to some weird limitation in Set Expressions an expression with a set element like -1 will not work properly so if you intend to use this bit value as a flag you should either invert the value so it gets 1 and 0 also in QlikView or you need to single quote the value in a set element like this:
Sum( { < IsPromotion={'-1'} Sales )
alternatively you can do
Sum( { < IsPromotion={1} Sales )
but
Sum( { IsPromotion={-1} Sales ) will NOT work...
In your load statement you could do:
-(Field1) AS Field1
True value in Qlik is -1 not 1 as usual ...
How i can Handle This...Any Suggestion?
The reason QlikView stores it as -1 and 0 is that it is able to make an efficient storage into bits because it use a symbol table. So the -1 and 0 as True() and False() values are more handy than 1 or 0.
Unfortunately due to some weird limitation in Set Expressions an expression with a set element like -1 will not work properly so if you intend to use this bit value as a flag you should either invert the value so it gets 1 and 0 also in QlikView or you need to single quote the value in a set element like this:
Sum( { < IsPromotion={'-1'} Sales )
alternatively you can do
Sum( { < IsPromotion={1} Sales )
but
Sum( { IsPromotion={-1} Sales ) will NOT work...
In your load statement you could do:
-(Field1) AS Field1
If you need to manage in qlik as a boolean value then leave it as is, otherwise add a new field (Integer) and write 1 instead of -1
(only a simple idea ... it depends on what you need)
SQLValue*(-1) as QVValue ?
I just came across this after having a similar problem after migrating from different versions of SQL. I do not believe this is correct.
"The reason QlikView stores it as -1 and 0 is that it is able to make an efficient storage into bits because it use a symbol table. So the -1 and 0 as True() and False() values are more handy than 1 or 0."
It is dependent on the driver and not qlik. With MS SQL, I found that the SQL native client driver will load a bit field as 0/1 and the OLEDB driver will load it as 0/-1.
If Qlik stores true() / false() it stores as hinted -1 / 0. But within each calculating only the 0 is relevant because this is false() and each other value regardless if the value is -1 or 1 or even 'a' is true().
This means within many scenarios the real value behind true() isn't relevant. Personally I would ensure that each stored boolean true() value is 1 - maybe forced per fabs() or ven with fabs(sign()) - because I use them often as flag-values for multiplications or to sum them.
- Marcus
Hi Marcus,
thanks for your response. The original poster had the same issue I was running into; it was not a true/false issue. He was expecting a 1/0 for a bit field and instead was getting a -1/0. This actually is not a Qlik issue, rather the driver which interprets a bit field differently.
Personally I would not have used a bit field, but this was code I inherited and we ran across this in a migration. I think there are many different solutions, two of which you mentioned: if you can, use the "0" rather than the -1/1, 2) use fabs function, 3) avoid bit fields.