Rounding of 4 decimal places doesn't work correctly

[#1]

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

[Anonymous]

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

[Anonymous]

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

[#4]

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.

[Anonymous]

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,

[johnw]

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.

[#7]

Thanks John, appreciate your help, it worked as used the workaround GPA*1000 or *1000000 to get the required results and not use the ROUND function which was causing all the issue.

Thanks again,

DD