7 Replies Latest reply: Jun 19, 2012 1:10 PM by Sebastian Pereira RSS

    Load Sentence changes SQL values

    Sebastian Pereira

      Hi all.

      I have to read the field "survey_sk" and "fronts" from SQL2005 database. "survey_sk" is an integer of 18 positions. If I use:

       

      SQL Select

           survey_sk,

           fronts

      From Table

      where survey_sk='634610963568975007';

       

      QV Loads the registers with this survey_sk (i know with the sum(fronts) expression), but the survey_sk field is empty. Now, if I try;

       

      Load

           num(survey_sk,'##################') as survey_sk

      SQL Select

           survey_sk,

           fronts

      From Table

      where survey_sk='634610963568975007';

       

      The same data is loaded, but survey_sk='63461096356897 4980' (instead of 63461096356897 5007)

       

      Now, if I put the where in Load instead of Select, QV doesn't find any register. Is Clear that Load sentence modifyes the data.

       

      Any suggestion to load correct data? Im using QV11.00.11282.0 SR1

        

       

      Thanks!!

        • Re: Load Sentence changes SQL values
          Erich Shiino

          Hi, QV works with a limit of 14 digits for a number.

          You can get some ways to handle it here:

           

          http://community.qlik.com/message/223100#223100

           

          I guess you can also try this:

           

          Load

               evaluate(survey_sk) as survey_sk

          SQL Select

               survey_sk,

               fronts

          From Table

          where survey_sk='634610963568975007';

           

          Hope it helps,

           

          Erich

            • Re: Load Sentence changes SQL values
              Sebastian Pereira

              Thanks Erich!, but I don't care if QV takes this field as Text...  In fact, I have tested it with Text().... And nothing happens...

               

              "Evaluate()", will give me the field as Integer? And in this Integer, what about the 14 digit limit? This Integer, is in normal format or in scientific notation?

               

              Thanks!!!

                • Re: Load Sentence changes SQL values
                  Erich Shiino

                  Hi,

                   

                  If you try the following script, you will create two fields:

                  T:

                  LOAD * INLINE [

                      Test

                      123456789123456789

                  ];

                   

                  LOAD Evaluate(Test) as EvalTest, Test resident T;

                  drop table T;

                   

                  The field Test is just a string but EvalTest is a number. If you include it in a list, you can see all the 18 digits, but when you write some expression with it, you will get the scientific notation (e.g. sum(EvalTest) ). So, I believe you are actually losing precision due to this notation, although QV can keep the original format when presenting just the field.

                   

                  Regards,

                   

                  Erich

                    • Re: Load Sentence changes SQL values
                      Sebastian Pereira

                      Erich, Thanks for your interest in this topic.

                       

                      In my case, I don't want to make expressions with this field, and because of it is better to have it as string.

                       

                      But, if we would need like a number, this thread could be useful for the comunity. So, I tryed with your suggestion, and the field has only Nulls.

                       

                      I think that the problem is in the transition of SQL Select to Load, because the "where" clause works good, the Load sentence keeps taking the field like a number and, because of its >14 digits, couldn't take like that.

                       

                      So, I can't understand why works well since the data comes from INLINE, and not from Select sentence.

                       

                      I have checked the SQL base, and the field is "BigInt" type. Using "CAST", I format it to "Char" type. I can now take it like number, with Evaluate or simply Num function. Did you used Evaluate with SQL data?

                • Re: Load Sentence changes SQL values
                  Sebastian Pereira

                  Finally, i used:

                   

                  Load *;

                  SQL Select

                       CAST (survey_sk as Char) as survey_sk,

                       fronts

                  From Table;

                   

                  As this way, QV reads the field with correct content, as Text, but correct.