3 Replies Latest reply: Apr 9, 2010 1:25 PM by John Witherspoon RSS

    ROUNDING DECIMALS

      Hi All,

       

      Please help :(.

      I am trying to get decimal values from database .

      In the load script i try to divide two decimal columns and get a value of 2.99.

      However i am getting rounded values in my report. i.e 3

      I have two bucket defined as 0-3 and 3-6.

      I need 2.99 under 0-3 bucket,however it falls under 3-6.

      Please help.

       

       

        • ROUNDING DECIMALS
          John Witherspoon

          It looks like you commented in the Wiki explaining mathematical errors in QlikView shortly after making this post. Did that seem to be the explanation for your problem? Were you able to apply the workaround mentioned there - to load your decimal fields in as integers instead? Do you still need help with this problem?

            • ROUNDING DECIMALS

              Yeah John,

              I took help from there .

              Yes it helped me a lot , but there is a small bug. I converted each of the columns to integer by first multiplying each and then dividing to get the ratio, as you had mentionedSmile

              But i am not able to figure out how many zeroes to multiply the column with i.e whether (column)* 100 or (column)*1000 or etc.

              My columns have a length of 9 and scale of 2 in database.

              Now i have two buckets : first bucket ->0-3 : defined as >=0 and <3 , second bucket ->3-6 : defined as >=3 and <6.

              My columns values are column1:205.2 and column2: 68.4 .

              My ratio is col1/col2. So if i multiply (column1*10000)/(column2*10000) , the value lies in 3-6 months bucket. But if i do

              (column1*100000)/(column2*100000),the value falls in 0-3 months bucket.

              Please help me. I am stuck :(.

               

               

                • ROUNDING DECIMALS
                  John Witherspoon

                  You need to do the multiplication before the data ever hits QlikView. If you want until the expression to do it, your data will already have been corrupted. By a length of 9 and a scale of two, do you mean your numbers are of format 1234567.89? If so, then you need to multiply by 100. You're multiplying by the number of decimal positions because you must have an integer to avoid possible corruption of the data value. But like I said, you need to do it as you read it in, as you pull from the source. So if you're pulling from an SQL database, for instance, you could do something like this:

                  LOAD *
                  ;
                  SQL SELECT
                  Column1
                  ,Column1 * 100 as Column1times100
                  ,Column2
                  ,Column2 * 100 as Column2times100
                  ...
                  ;

                  For list boxes and the like, you display the raw Column1. But when you need mathematical precision, you use Column1times100. So when you're setting up your buckets, you'd use something like this:

                  class(Column1times100/Column2times100,3)