4 Replies Latest reply: May 11, 2012 1:36 PM by Jackie_C RSS

    NaN handling in 2.2.2 EQL?

    Jackie_C
      I know in 2.3, you can use the CASE expression to avoid a NaN calculation.
      Is there a known workaround in 2.2.2's EQL to avoid displaying a NaN?
        • 1. Re: NaN handling in 2.2.2 EQL?
          Ryan S.-Oracle
          Jackie,

          2.2 allows for NaN calculations. If you are looking to replicate the functionality of case we frequently saw people leverage coalesce in creative ways.

          From the document (http://lat22.endeca.com/help/index.jsp?topic=/com.endeca.Latitude.StudioUsers.doc/src/cdfp_analytics_handling_nan.html)

          For example, NaN, inf and -inf values could arise in your LQL calculations when:
          A zero divided by zero results in NaN
          A positive number divided by zero results in inf
          A negative number divided by zero results in -inf


          - Ryan
          • 2. Re: NaN handling in 2.2.2 EQL?
            Jackie_C
            Thanks Ryan.

            I know about the handling of NaN, inf, and -inf.
            I guess I was hoping for some examples of creative ways to get around the divide by 0 scenario.

            If you have some samples, I'd really appreciate it.

            Thanks!
            Chris
            • 3. Re: NaN handling in 2.2.2 EQL?
              920261
              I've seen COALESCE used for this purpose in 2.2.x. You need to create a DEFINE statement that eliminates groupings that would put zero in the denominator.
              In this example, I want to calculate SUM(y) / SUM(x) and ensure that I don't divide by zero.


              DEFINE NonZeroSumX AS SELECT
              SUM(x) AS SumX
              GROUP by A, B
              HAVING SumX > 0;

              RETURN Results AS SELECT
              SUM(y) AS SumY,
              COALESCE(NonZeroSumX[A,B].SumX, 1.0) AS SumX,
              SumY / SumX AS Test
              GROUP BY A, B;

              NonZeroSumX[A,B] will return null if there is no value in the lookup table and the coalesce will default to 1.0.

              Dave
              • 4. Re: NaN handling in 2.2.2 EQL?
                Jackie_C
                Thanks Dave.

                That example works perfectly for me in my situation.

                Cheers!
                Chris