topic Re: How to Avoid Counting Nulls in a DISTINCT Expression in Administrative Discussions

How to Avoid Counting Nulls in a DISTINCT Expression

amit_kothari — Fri, 08 Apr 2022 15:57:23 GMT

Using DISTINCT() with an IF() in a formula expression leads to incorrect computation as nulls are not handled by default for an on-premise Incorta environment.

Re: How to Avoid Counting Nulls in a DISTINCT Expression

amit_kothari — Tue, 07 Mar 2023 18:59:00 GMT

To avoid counting nulls in a formula using an IF expression returning numbers add a default of null(0.0) , for eg -DISTINCT(IF(Test.Table1.OrderStatus='Open',1,null(0.0) ))

Steps for Incorta Cloud cluster from 2022.12.0 onwards :

Set this null handling option in cmc and restart the services - https://docs.incorta.com/cloud/whats-new-2022-12-0#handling-null-values

Steps for On premise install of Incorta :

Navigate to <incorta home>/IncortaNode/services folder.
Run the command in linux : cat services.index
See which is the folder for Analytic services
Navigate to <Analytic services>/incorta folder
Edit the engine.properties file to add the line: engine.disable_null_handling=false
Save the file and navigate to the CMC url and restart the Analytic service.
Verify the dashboard.
---
Or to do it via the analyzer, follow this video tutorial: https://youtu.be/QG-AJJh0OHM

Re: How to Avoid Counting Nulls in a DISTINCT Expression

natarajasokan — Thu, 13 Jul 2023 14:38:33 GMT

@amit_kothari - Can you please let me know how to handle it if we are doing a distinct of a string value. We have datatype mismatch issue.

Eg- Table1.Order_number is a string

DISTINCT(IF(Test.Table1.OrderStatus='Open',Table1.Order_number,null(0.0) ))