14 Replies Latest reply: Jan 17, 2018 5:48 AM by Mikel De RSS

    Question regarding set analysis

    Mikel De

      Hello,

       

      I am a bit confused about a set analysis expression that does not work as expected when the fields in a table are empty. Here a sample script:

       

      LET vThisYear=Year(Today());
      
      Employees:
      LOAD 
      *
      INLINE 
      [
      ID,Name,Salary
      100,Person A, 110000
      200,Person B, 120000
      300,Person C, 130000
      400,Person D, 140000
      500,Person E, 150000
      ]
      (DELIMITER IS ',');
      
      
      Hired:
      LOAD 
      *
      INLINE 
      [
      ID,Hired
      300,2017
      400,2017
      500,2018
      ]
      (DELIMITER IS ',');
      
      
      
      
      

       

      If I use the expression: Count({<Hired={$(vThisYear)}>}ID) in a KPI object, it correctly returns 1.

       

      However, if the Hired table in the script is empty (which might happen) like this:

       

      Hired:
      LOAD 
      *
      INLINE 
      [
      ID,Hired
      ]
      (DELIMITER IS ',');
      

       

      the same KPI object would return 5 instead of 0.

       

      My questions are:

      A) Why does this happen?

      B) How to handle this so that the expression returns 0 when the table is empty?

       

      Thank you!