topic Force a null value? in Dashboards & Analytics Discussions

Force a null value?

marcpaige — Mon, 09 May 2022 16:11:47 GMT

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.

Re: Force a null value?

dylanwan — Thu, 19 May 2022 19:52:31 GMT

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

Re: Force a null value?

marcpaige — Thu, 19 May 2022 21:37:57 GMT

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

Re: Force a null value?

amit_kothari — Fri, 20 May 2022 16:29:57 GMT

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

Re: Force a null value?

marcpaige — Fri, 20 May 2022 17:02:51 GMT

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, "-###"))))