How to Avoid Counting Nulls in a DISTINCT Expression

[amit_kothari]

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.

[amit_kothari · Accepted solution]

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 :

1. 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 :

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

[natarajasokan]

@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) ))