Force a null value?

[marcpaige]

I am looking for the ability to force a null value from an if() function. Here is the example:

int(

    if(

        isAlpha(

            desktop.PCAgeReference.Estimated_Age

        ), 

        0,  <-- This is where I want to return NULL

        abs(

            parseDouble(

                desktop.PCAgeReference.Estimated_Age, 

                "-###"

            )

        )

    )

)

The issue is the filter. I want to allow the user to be able to filter using number comparisons. The base data type is 'string' and is a mixture of negative numbers and strings (N/A, Unknown). This formula field gives me the integer values, but I really don't want to use 0 for N/A and Unknown. I prefer to have NULL if possilbe.

[dylanwan]

It may work if you change 0 to 1/0 (one divided by zero) and move int() on abs only.

[marcpaige]

I was hopeful, but it still resolved to 0. I will make a request for a new function that provides NULL.

[amit_kothari]

We have a support for null() function, refer to https://community.incorta.com/t5/administrative-discussions/how-to-avoid-counting-nulls-in-a-distinc...

[marcpaige]

Thanks, Amit, but I must have done something wrong. I added the line referenced to the engine.properties file and restarted the analyzer. Is there a place where I may read the documentation on the null() function? I cannot find a reference in the built-in functions.

Here is the amended formula:

if(isAlpha(desktop.PCAgeReference.Estimated_Age), null(0.0), 

    int(abs(parseDouble(desktop.PCAgeReference.Estimated_Age, "-###"))))