Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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))
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.
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))
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