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

    Set expression, aggregation, and intersections

    Al Sellaro

      Hi

       

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

       

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

       

      Thanks