1 Reply Latest reply on Jan 1, 2017 9:29 AM by Norbert Maijoor

    How to compare rows to determine when a client dropped out of a process

    Christian Cox

      Hi,

       

      I am trying to identify and flag clients that have dropped
      out of a process after completing a certain step.

       

      In more detail:

      Client_ID’s that have completed step one of a process 

      (AND have not completed step 2

      OR have completed step 2 at an earlier date/time than when
      they completed step 1)

       

      If the above condition is met I want to set the calculated field ‘Dropped-Out” to  “TRUE” for the “Step 1” row

      If the row does not meet the above condition then I want to
      set the “Dropped-Out” field to “FALSE”.

       

      In the below data set

      • The “step 1” row for “Client_ID 2” should have
        “Dropped-Out” set to “TRUE” as the “step 2
        row” for “Client_ID 2” has an earlier date/time.
      • The “step 1” row for “Client_ID 3” should have
        “Dropped-Out” set to “TRUE” as there is no “step 2” row for ‘Client_ID 3”.
      • The “step 1“ row for “Client_ID 5” should have “Dropped-Out” set to “TRUE” as there is no “step 2” row for “Client_ID 5”.

       

      Example Data Set:

       

      Client_ID

      Step
       

      Date/Time

      Dropped-
        out

      1

      1

      01/01/2016 13:00:00

      FALSE

      1

      2

      01/01/2016 13:01:00

      FALSE

      2

      1

      02/01/2016 13:00:00

      TRUE

      2

      2

      01/01/2016 13:00:00

      FALSE

      3

      1

      01/01/2016 13:00:00

      TRUE

      3

      3

      01/01/2016 13:01:00

      FALSE

      4

      1

      01/01/2016 13:00:00

      FALSE

      4

      2

      01/01/2016 13:00:00

      FALSE

      5

      1

      01/01/2016 13:00:00

      TRUE

       

      Thanks,

       

      Christian.