7 Replies Latest reply: Apr 16, 2018 1:32 PM by Al Sellaro RSS

    Set expression, aggregation, and intersections

    Al Sellaro



      I have a data table which has three fields: Client, Problem_ID, Ticket, and Status. I need a set expression to count the number of records that match the all of the following criteria:


      • Distinct client names
      • Problem_ID = 123 and Status <> W
      • Problem_ID = 456 and Status <> W


      I tried the following, but it is obviously wrong:


      count({$ <problem_id = 123, status <> 'W'> + <problem_id = 456, status <> 'W'>} DISTINCT client)


      If I try using the intersection operator instead, the result is zero.


      With the example data below, the expected counting should be 2 (as only Client A and Client D match the criteria).


      Client A123123456A
      Client A456789012B
      Client B123131517A
      Client C456242628A
      Client D123654321A
      Client D456876543B