5 Replies Latest reply: Nov 16, 2010 5:25 AM by Roland Kunle RSS

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

    stolkin1

      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.

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

          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.

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

              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))

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

                  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.

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

                      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))

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

                          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