6 Replies Latest reply: Oct 17, 2012 6:41 PM by Dipti Desai RSS

    Rounding of 4 decimal places doesn't work correctly

      Hi all,

       

      I have a column called CumGPA which has all the values from 0.0000 to 4.9999. So to verify the results and get the round value of it have added another column as:

       

      round( CumGPA , 0.1 ) as CumGPAMark,

       

      What I need is to achieve is to get another column called CumGPAGrade which will show the Grades if it falls within this range of values,

      Grade should be A if all the range of CumGPA values fall between 4.0 and 3.8.

       

      Code 1: So the below code1 works fine for all values except doesn't work only for 1.7, 2.3 and 3.3,

      Because 1.7 should be C- but falls in C, same with 2.3 falls in B- instead of C+ and 3.3 falls in A- instead of B+.

       

      /////////////////////////////////////////////////////////////////////////////////////

      Code 1:

      If (round( CumGPA , 0.1 ) <= 1.0, 'D',

      If (round( CumGPA , 0.1 ) <= 1.3, 'D+',

      If (round( CumGPA , 0.1 ) <= 1.7, 'C-',

      If (round( CumGPA , 0.1 ) <= 2.0, 'C',

      If (round( CumGPA , 0.1 ) <= 2.3, 'C+',

      If (round( CumGPA , 0.1 ) <= 2.7, 'B-',

      If (round( CumGPA , 0.1 ) <= 3.0, 'B',

      If (round( CumGPA , 0.01 ) <= 3.3, 'B+',

      If (round( CumGPA , 0.1 ) <= 3.7, 'A-',

      If (round( CumGPA , 0.1 ) <= 4.0, 'A',

      If (round( CumGPA , 0.1 ) <= 5.0, 'A+',

      )))))))))))) as CumGPAGrade

       

      Code 2: So the below code2 works fine for all values except doesn't work only for 1.7, 2.3 and 3.3,

      The Grade column is null for these 1.7, 2.3 and 3.3  values.

      /////////////////////////////////////////////////////////////////////////////////////

      or Code2:

       

      intervalmatch1:

       

      load * inline [

      from1, to1, CumGPAGrade

      4.1, 5.0, A+

      3.8, 4.0, A

      3.4, 3.7, A-

      3.1, 3.3, B+

      2.8, 3.0, B

      2.4, 2.7, B-

      2.1, 2.3, C+

      1.8, 2.0, C

      1.4, 1.7, C-

      1.1, 1.3, D+

      0.1, 1.0, D

      0.0, 0.0, F

      ];

       

      Left Join (Graduation)

      INTERVALMATCH (CumGPAMark)

      LOAD

      from1,

      to1

      resident intervalmatch1;

       

      Left Join (Graduation)

      LOAD

      *resident intervalmatch1;

       

      //drop Fields from1, to1

      //from Graduation;

       

      drop Table intervalmatch1;

       

      //////////////////////////////////////////////////////////////////////

       

      Have been changing code around and tried to do lot of work arounds but no success. If anyone could help with this or have any different ideas to share that would be great.

       

      Would appreciate all the help, and thanks for your valuable time.

       

      Thanks,

      DD

        • Re: Rounding of 4 decimal places doesn't work correctly
          Vinod Kakarla

          Dipti,

           

          You should define the expression to have range lke

           

          If (round( CumGPA , 0.1 ) >= 0 and round( CumGPA , 0.1 ) <= 1.0, 'D',

          If (round( CumGPA , 0.1 ) >= 1.0 and round( CumGPA , 0.1 ) <= 1.3 , 'D+',

          If (round( CumGPA , 0.1 ) >= 1.3 and round( CumGPA , 0.1 ) <= 1.7, 'C-',

          If (round( CumGPA , 0.1 ) >= 1.7 and round( CumGPA , 0.1 ) <= 2.0, 'C',

          If (round( CumGPA , 0.1 ) >= 2.0 and round( CumGPA , 0.1 ) <= 2.3, 'C+',

          If (round( CumGPA , 0.1 ) <= 2.3 and round( CumGPA , 0.1 ) <= 2.7, 'B-',

          If (round( CumGPA , 0.1 ) <= 2.7 and round( CumGPA , 0.1 ) <= 3.0, 'B',

          If (round( CumGPA , 0.1 ) <= 3.0 and round( CumGPA , 0.01 ) <= 3.3, 'B+',

          If (round( CumGPA , 0.01 ) <= 3.3 and round( CumGPA , 0.1 ) <= 3.7, 'A-',

          If (round( CumGPA , 0.1 ) <= 3.7 and round( CumGPA , 0.1 ) <= 4.0, 'A',

          If (round( CumGPA , 0.1 ) <= 4.0 and round( CumGPA , 0.1 ) <= 5.0, 'A+',

          )))))))))))) as CumGPAGrade

           

          Thanks,

          Vinod

            • Re: Rounding of 4 decimal places doesn't work correctly
              Vinod Kakarla

              Little correction

               

              You should define the expression to have range lke

               

              If (round( CumGPA , 0.1 ) >= 0 and round( CumGPA , 0.1 ) <= 1.0, 'D',

              If (round( CumGPA , 0.1 ) >= 1.0 and round( CumGPA , 0.1 ) <= 1.3 , 'D+',

              If (round( CumGPA , 0.1 ) >= 1.3 and round( CumGPA , 0.1 ) <= 1.7, 'C-',

              If (round( CumGPA , 0.1 ) >= 1.7 and round( CumGPA , 0.1 ) <= 2.0, 'C',

              If (round( CumGPA , 0.1 ) >= 2.0 and round( CumGPA , 0.1 ) <= 2.3, 'C+',

              If (round( CumGPA , 0.1 ) >= 2.3 and round( CumGPA , 0.1 ) <= 2.7, 'B-',

              If (round( CumGPA , 0.1 ) >= 2.7 and round( CumGPA , 0.1 ) <= 3.0, 'B',

              If (round( CumGPA , 0.1 ) >= 3.0 and round( CumGPA , 0.01 ) <= 3.3, 'B+',

              If (round( CumGPA , 0.01 ) >= 3.3 and round( CumGPA , 0.1 ) <= 3.7, 'A-',

              If (round( CumGPA , 0.1 ) >= 3.7 and round( CumGPA , 0.1 ) <= 4.0, 'A',

              If (round( CumGPA , 0.1 ) >= 4.0 and round( CumGPA , 0.1 ) <= 5.0, 'A+',

              )))))))))))) as CumGPAGrade

               

              Thanks,

              Vinod

            • Re: Rounding of 4 decimal places doesn't work correctly

              Thanks for a very good suggestion it works like a charm for everything now includes 1.7, 2.3 and 3.3 and falls in its appropriate Grades bucket except few nulls.

               

              I still get couple results with Grade column as nulls for 1.7, 3.3 and 2.3 (meaning it’s having Grade column as null for values like CumGPA (1.7280, 2.3220, 2.3130) etc.

               

              Below is my updated code, which I’m still revising to figure out why Grade column is null for those values. If you have any ideas please suggest that would be very helpful.

               

              If (round( CUM_GPA , 0.1 ) = 0.0, 'F',
              If (round( CUM_GPA , 0.1 ) >= 0.1 and round( CUM_GPA , 0.1 ) <= 1.0, 'D',
              If (round( CUM_GPA , 0.1 ) >= 1.1 and round( CUM_GPA , 0.1 ) <= 1.3 , 'D+',
              If (round( CUM_GPA , 0.1 ) >= 1.4 and round( CUM_GPA , 0.01 ) <= 1.7, 'C-',
              //If (round( CUM_GPA , 0.0001 ) = 1.7, 'C-',
              //If (round( CUM_GPA , 0.001 ) = 1.7, 'C-',
              //If (round( CUM_GPA , 0.1 ) = 1.7, 'C-',
              If (round( CUM_GPA , 0.1 ) >= 1.8 and round( CUM_GPA , 0.1 ) <= 2.0, 'C',
              If (round( CUM_GPA , 0.1 ) >= 2.1 and round( CUM_GPA , 0.1 ) <= 2.3, 'C+',
              If (round( CUM_GPA , 0.1 ) >= 2.1 and round( CUM_GPA , 0.01 ) <= 2.3, 'C+',
              If (round( CUM_GPA , 0.1 ) >= 2.4 and round( CUM_GPA , 0.1 ) <= 2.7, 'B-',
              If (round( CUM_GPA , 0.1 ) >= 2.8 and round( CUM_GPA , 0.1 ) <= 3.0, 'B',
              If (round( CUM_GPA , 0.1 ) >= 3.1 and round( CUM_GPA , 0.1 ) <= 3.3, 'B+',
              If (round( CUM_GPA , 0.1 ) >= 3.1 and round( CUM_GPA , 0.01 ) <= 3.3, 'B+',
              If (round( CUM_GPA , 0.1 ) >= 3.4 and round( CUM_GPA , 0.1 ) <= 3.7, 'A-',
              If (round( CUM_GPA , 0.1 ) >= 3.8 and round( CUM_GPA , 0.1 ) <= 4.0, 'A',
              If (round( CUM_GPA , 0.1 ) >= 4.1 and round( CUM_GPA , 0.1 ) <= 5.0, 'A+',
              ))))))))))))))

              //)))

              as CumGPAGrade,

               

              Thanks and appreciate your valuable time.

                • Re: Rounding of 4 decimal places doesn't work correctly
                  Vinod Kakarla

                  if you take out rounding it should work,

                   

                  try this

                   

                   

                   

                  If (( CUM_GPA , 0.1 ) = 0.0, 'F',

                  If (( CUM_GPA , 0.1 ) >= 0.1 and ( CUM_GPA , 0.1 ) <= 1.0, 'D',

                  If (( CUM_GPA , 0.1 ) >= 1.1 and ( CUM_GPA , 0.1 ) <= 1.3 , 'D+',

                  If (( CUM_GPA , 0.1 ) >= 1.4 and ( CUM_GPA , 0.1 ) <= 1.7, 'C-',

                  //If (( CUM_GPA , 0.0001 ) = 1.7, 'C-',

                  //If (( CUM_GPA , 0.001 ) = 1.7, 'C-',

                  //If (( CUM_GPA , 0.01 ) = 1.7, 'C-',

                  If (( CUM_GPA , 0.1 ) >= 1.8 and ( CUM_GPA , 0.1 ) <= 2.0, 'C',

                  If (( CUM_GPA , 0.1 ) >= 2.1 and ( CUM_GPA , 0.1 ) <= 2.3, 'C+',

                  If (( CUM_GPA , 0.1 ) >= 2.1 and ( CUM_GPA , 0.01 ) <= 2.3, 'C+',

                  If (( CUM_GPA , 0.1 ) >= 2.4 and ( CUM_GPA , 0.1 ) <= 2.7, 'B-',

                  If (( CUM_GPA , 0.1 ) >= 2.8 and ( CUM_GPA , 0.1 ) <= 3.0, 'B',

                  If (( CUM_GPA , 0.1 ) >= 3.1 and ( CUM_GPA , 0.1 ) <= 3.3, 'B+',

                  If (( CUM_GPA , 0.1 ) >= 3.1 and ( CUM_GPA , 0.01 ) <= 3.3, 'B+',

                  If (( CUM_GPA , 0.1 ) >= 3.4 and ( CUM_GPA , 0.1 ) <= 3.7, 'A-',

                  If (( CUM_GPA , 0.1 ) >= 3.8 and ( CUM_GPA , 0.1 ) <= 4.0, 'A',

                  If (( CUM_GPA , 0.1 ) >= 4.1 and ( CUM_GPA , 0.1 ) <= 5.0, 'A+',

                  ))))))))))))))

                   

                  as CumGPAGrade,

                    • Re: Rounding of 4 decimal places doesn't work correctly
                      John Witherspoon

                      I agree with removing the rounding if that's allowed.  However, I know that some schools actually do roud, so for instance a test score of 89.6% would count as an A since it rounds up.  Perhaps GPAs work similarly.

                       

                      I suspect the problem IS the rounding, though.  QlikView rounding (and anything dealing with non-integers) is subject to mathematical error due to its use of binary numbers internally.  I wrote an extensive document on exactly what was going on a few years ago, but it seems to have disappeared.  I also appear to have ONLY written it on the forum, as I appear to have no copy on my hard drive.

                       

                      The workaround is to deal in integers only.  So instead of storing a GPA, store a GPA*1000 or *1000000 or whatever you need in the actual data model so that you're only storing integers.  Only convert back to a regular GPA for display.  It may or may not be worth it for any specific case.  I've never bothered with the workaround.  I just accept that QlikView is going to get some mathematics wrong sometimes.

                       

                      Much easier if it's OK to simply not round.