Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

6 Replies
vkakarla
New Contributor III

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

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

vkakarla
New Contributor III

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

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

Not applicable

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.

vkakarla
New Contributor III

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

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,

MVP
MVP

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

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.

Not applicable

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

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

Community Browser