topic SQL select iterprets 1 as -1 in QlikView

SQL select iterprets 1 as -1

Thu, 08 Dec 2011 18:57:35 GMT

Hello All:

I have a SQL field that only contains the values -1, 0 and 1. In the resulting QlikView data, I'm only getting -1 and 0. All of the rows with a positive one in SQL have been converted to -1 in QlikView. Am I missing something to maintain the 3 distinct values? I have other SQL tables with mixtures of negative and positive numbers in them that are working - the only difference is a much wider range of values.

Here's my SQL statement: The offending field is the one I'm aliasing as Inter_Company_Key.

SQL SELECT "Customer_Group_Key",

"Customer_Key",

"Customer_Name",

"Customer_Tier_Key",

"Internal_Customer_Key" as Inter_Company_Key,

"Record_Update_Date" as Customer_Last_Updated.

"Superior_Customer_Key"

FROM EODS.dbo."CUSTOMER_DIM"

Many Thanks for your help,

Simon

SQL select iterprets 1 as -1

Thu, 08 Dec 2011 19:54:35 GMT

Try timesing by 1, or adding 0 to force it to have a look at what it's doing. Most unusual problem though.

"Internal_Customer_Key" * 1 as Inter_Company_Key, or

"Internal_Customer_Key" +0 as Inter_Company_Key,

Regards,

Erica

Re: SQL select iterprets 1 as -1

Miguel_Angel_Baeyens — Thu, 08 Dec 2011 20:25:25 GMT

Hi Erica,

Did you try LOADing the fields?

Table:
LOAD Customer_Group_Key,
     Customer_Key,
     Customer_Name,
     Customer_Tier_Key,
     Inter_Company_Key,
     Customer_Last_Updated,
     Superior_Customer_Key;
SQL SELECT "Customer_Group_Key",
   "Customer_Key",
   "Customer_Name",
   "Customer_Tier_Key",
   "Internal_Customer_Key" as Inter_Company_Key,
   "Record_Update_Date" as Customer_Last_Updated.
   "Superior_Customer_Key"
FROM EODS.dbo."CUSTOMER_DIM"

I don't know if it's related to the driver or that QlikView interprets the field as true/false (although there are no boolean fields as such in QlikView), where false is 0 and true everything else (usually -1 or 1).

Hope that helps.

Miguel

SQL select iterprets 1 as -1

Thu, 08 Dec 2011 21:29:53 GMT

I think I have a line on the issue... The field is a not null bit in one place and a null integer in 2 other places. QlikView is getting confused about what context to show a null or a minus one from one place when there is a zero in another. Great; QlikView is helping me find data structure problems in the Data Warehouse!

Miquel: What is the potention advantage to using the load statement before a SQL select? By the way, I'm using an OLE connection to SQL.

Cheers and thanks all,

Simon

Re: SQL select iterprets 1 as -1

Miguel_Angel_Baeyens — Thu, 08 Dec 2011 22:27:45 GMT

Hi Simon,

Using the LOAD, although not required, gives you the QlikView soul to your data alowing you to really control what data is put into QlikView memory rather than controlling what you pull from the data source (in this case, a SQL statement, but there are a few more, like an excel file, or a hardcoded mapping table). There are dozens of functions you can use in QlikView, conditionals, accumulations, string modifiers, formatting, date and time... that otherwise would require hours of implementation in the data source.

I always use the LOAD statement and I'd never think of a new project without using it in the script.

There are some interesting posts in the QlikCommunity about the use and advantages of LOAD, but I recommend you to check this thread.

Hope that helps.

Miguel.

Re: SQL select interprets 1 as -1

youcantryreachingme — Wed, 22 May 2019 00:59:54 GMT

Passing data from a field of one type (say bit) into a field of another type (say int) is no crime. Your system will implement default type conversion rules, or fail, depending on how it is configured, or you will explicitly cast your type conversion.

What astounds me is that QlikView should take a bit data field and change the values.

An interesting article (link below) suggests QlikView internally uses only 2 data types: strings and numerics. That being the case I would have thought the values '0' and '1' (from a bit column in SQL Server, say) are very clearly numeric. There is no justification for modifying those to '0' and '-1'. Doing so suggests QV actually attempts to track a third data type, not mentioned in the article, which is true and false represented - inexplicably - by -1 and 0, respectively.

Even if you accept QlikView attempting data conversions, why, oh why, did they settle on '-1' to represent 'true' when every other programming language I can think of uses '1' for same, and '-1' to represent an error condition? I mean QlikView is saying "I understand that 1 represents true to you, so I will represent your true as -1".

https://www.resultdata.com/qlikview-data-types/