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.

5replies 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.
    Like
      • Prudhvi Raj
      • NJM
      • Prudhvi_Raj
      • 2 yrs ago
      • Reported - view

      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.

      Like
      • Jonathan Chang
      • NortonLifeLock
      • Jonathan_Chang
      • 7 mths ago
      • Reported - view

      Amit Kothari 

      How does this setting change affect other parts of the insight?   In other words,  I still want the null values showing up in situations like a bar chart displaying transaction by product type - I want to show the transactions with Null product category.

      I want to verify that this setting change only affects the distinct count situation and not other situations as I describe above.

      Like
    • Jonathan Chang This will only affect the formula using null() 

      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.

    Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 7 mths agoLast active
  • 5Replies
  • 545Views
  • 3 Following

Product Announcement

Incorta 5 is now Generally Available