compare string > 0 produces wrong result when just one value is selected from a dimension

[#1]

Simplest case that shows the problem:
In my tiny test Qlikview application in a pass/fail course a grade of 'P' means you get credit for the course, but a grade of 'F' means you do not get credit.

Contents of the input file: tiny.xls has just these 3 lines. The first line is the field headers:

credits grade
4 F
4 P

In a Chart I wrote this expression: sum(if(grade='P' or grade>0,credits,0))

When I do a clear to select all the values the results seem correct.

However, when I select just the value 'F' in the list box for the grade dimension it shows 4 credits, rather than 0 as expected.

Even this simpler expression has the same problem: sum(if(grade>0,credits,0))

Why does selecting just one value cause this?

Here is the contents of the edit script:

LOAD credits,
grade
FROM
C:\_my\tiny.xls
(biff, embedded labels);

The output to Excel after I do a Clear seems correct. Note that the P gets 4 credits, and the F gets 0, as desired. (I formatted for this post using fixed width tt tag)

credits grade Sum(credits) sum(if(grade>0,credits,0)) sum(if(grade='P' or grade>0,credits,0))
8 0 4
4 F 4 0 0
4 P 4 0 4

The problem is that if just the single value 'F' is selected from the list of grades, the last two columns seem wrong! Here is the export to Excel:

credits grade Sum(credits) sum(if(grade>0,credits,0)) sum(if(grade='P' or grade>0,credits,0))
4 4 4
4 F 4 4 4

If this behavior is "by design" can someone explain the reason, or point me to a place in the Reference manual.

This might not be relevant, but I did look in the reference manual which says:
< Less than
A numeric comparison is made if both operands can be interpreted numerically.

But will the letter F or P ever be interpreted numerically? If so, when and what number is it? Or does this < comparison ever treat the number 0 as a string?

Background: I am new to Qlikview but an experienced developer. I am running the personal editon for 32 bit Windows, version 10.00.87 15.5 IR. To learn Qlikview I created a simple Chart that computes a grade point average for college students. This example is a simplified version that demonstrates the problem.

[#2]

Hi,

what do you mean with grade> 0 ? As grade contains text the correct sysntax would be "LEN(grade) > 0", but I think this is not what you want. As far as I understad your problem, the expression should be without that:

sum(if(grade='P', credits,0))

Regards Roland

P.S:

Don't hesitate to send an exam application, if this doesn't fit.

[#3]

Dear Roland (and eveyone),

The grade dimension contains a mix of numbers and strngs. Most of the grades are numeric, e.g. 4.0 means an A, 3.0, means a B, etc. In the example data I only showed one P and one F because that was enough to illustrate the problem. A student does not get credit if their numeric grade is 0. I really do want the expressIon that means: sum(if(grade='P' or grade>0,credits,0))

[#4 · Accepted solution]

Hi Steven,

sorry, i was looking in another direction In this situation make it clearer for QV and use the num()-function:

sum(if(grade='P' or num(grade)>0,credits,0))

I am sure this works as you expect it.

[#5]

Thanks. Adding the sum() function did work. But I have 2 follow up questions.

1. Why is sum needed here? The manual says that the comparison operators <, > etc. will try to interpret each operand numerically.

2. Performance. Most grades will be numbers. A few will be P, F, or I (meaning pass, fail, or incomplete). Adding sum() around grade will slow down the evaluation at least a little. I think the following two expressions wil be equivalent. is there any way easy to determine which will be faster? (Here "easy" means by an analysis rather than by running a performance test, whose results would depend on the specifics of the system and the test data.)

a. sum(if(grade='P' or num(grade)>0,credits,0))

b. sum(if(grade='I' or grade='F' or grade=0,0,credits))

[#6]

Hello Steven,

1. You need as you say correctly the num() to do an explicit "typecast" to compare the numeric values. BTW: I think you mean num() when you write sum(), right ?

2. I think both expression would do their job as expected. In a) you do an explicit num compare (which you need when using < or < ). In b) it is done implicit .

3. Concerning the performance, I think it is a minority topic you are looking at. This calc is done in RAM and its done only (and only the first time) when the correspondending chart(s) are shown. There are a lot of interessting perfomance threads in the forum. Using SET analysis versus using if-Statements. Or (what I like) precalculation of some values during load versus wasting enduser time for expression calculation. Or the calc of variables, which is done more often.

Hope I could give you some new aspects as you are an experienced developer to find out very usefull things.

Roland