4 Replies Latest reply: Dec 8, 2011 5:27 PM by Miguel Angel Baeyens de Arce RSS

    SQL select iterprets 1 as -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

        • SQL select iterprets 1 as -1

          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 de Arce

              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

                  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 de Arce

                      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.