3 Replies Latest reply: Jan 22, 2016 4:05 PM by Samuel Lin RSS

    Count the Latest Date... & Delete Canceled Events

    Samuel Lin

      Hi there Qlik Experts,

       

      Say I have a dataset:

       

      ID, isCurrentEmployment, Employment StartDate

       

      where ID is unique, isCurrentEmployment is binary, StartDate is Date. Each Person may have multiple employment entry, how do I do a count of ID that isCurrentEmployment =1 and the lastest(or earliest) StartDate?

       

      Also, say I have another dataset:

       

      Event_ID, Transaction_Type, Date

       

      For simplicity, Event_ID is unique to each event, Transaction_Type can be "Scheduled", "Canceled", "Completed"

       

      so it's possible that an event was scheduled and then got canceled, how do I do a count of "Scheduled" events less the "Canceled"?

       

      To make this more complicated, if it's possible to have "Scheduled" (01/01/2016) --> "Canceled" (01/02/2016) --> "Scheduled" (01/03/2016), so eventually this event is still Scheduled, is there a different way to do the count?

       

      Thanks much!!

      Samuel