SQL select iterprets 1 as -1

[#1]

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

[#2]

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

[Miguel_Angel_Baeyens]

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

[#4]

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

[Miguel_Angel_Baeyens]

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.

[youcantryreachingme]

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/