1

How to avoid counting Nulls in a DISTINCT expression

Using DISTINCT() with an IF() in a formula expression leads to incorrect computation as nulls are not handled by default.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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:

    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.
    Reply Like
    • Amit Kothari 

      I would like to know if we can achieve below count in incorta.

      select count(*) from table a where a.amount <100
       

      I worte below function in incorta to replicate above query

      count(If ( and(table.amount<100, table.amount>0),1,null()))

      but still count function is counting all the rows.

      Reply Like
  • Please check if the insight is  of type 'Data Field' or Aggregated  and if you have added the above line in the engine.properties.

    Reply Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 6 mths agoLast active
  • 3Replies
  • 302Views
  • 2 Following