This content has been marked as final. Show 4 replies
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
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.