4 Replies Latest reply on May 11, 2012 6:36 PM by Jackie_C

# NaN handling in 2.2.2 EQL?

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?
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?
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?
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?
Thanks Dave.

That example works perfectly for me in my situation.

Cheers!
Chris