Forum Discussion

ToreVingare's avatar
ToreVingare
Frequent Visitor
5 years ago
Solved

Process mining gaps and islands

Hello! 

 

I'm working on a process mining dashboard at my hospital. I have created all the timestamps that happens to our patient in the patientgroup but I can't succeed with the creation of a ID for every connective period in the date per patient. All rows in the data have a activity, start time, end time, location and a person identificationsnumber. 

 

Short example:

Person*******Activite********Start********************End*******************Location**************
Person 1Surgery2019-06-05 17:292019-06-05 18:02Surgery 1
Person 1Ward2019-06-05 18:032019-06-06 22:44Ward 135
Person 1Ward2019-06-06 22:452019-06-14 10:00Ward 354
Person 1Ward2020-03-26 10:542020-03-26 16:48Ward 237
Person 1Ward2020-03-26 16:492020-03-27 15:45Ward 236
Person 1Ward2020-03-27 15:462020-04-03 12:29Ward 303
Person 1Ward2020-04-04 04:512020-04-04 15:40Ward 303

 

What I'm trying to achieve is this:

Person*******Activite********Start*******************End********************Location************Group*****
Person 1Surgery2019-06-05 17:292019-06-05 18:02Surgery 11
Person 1Ward2019-06-05 18:032019-06-06 22:44Ward 1351
Person 1Ward2019-06-06 22:452019-06-14 10:00Ward 3541
Person 1Ward2020-03-26 10:542020-03-26 16:48Ward 2372
Person 1Ward2020-03-26 16:492020-03-27 15:45Ward 2362
Person 1Ward2020-03-27 15:462020-04-03 12:29Ward 3032
Person 1Ward2020-04-04 04:512020-04-04 15:40Ward 3032 (3)

 

The two rows marked with bold is the end and start of a new group. The first three rows is in a consecutive order. End time is just before the next rows start time. I want the first three rows to have the same identifier (1) and the next three rows a new identifier (2). 

 

The last row is just a bonus but if I could specifie the "threshold" for what I think is consecutive that would be a plus but absoluty not necessay. Lets say that if a new row is less the 12 hours later then accept is as the same group. Something like that. But just a bonus!

 

Each person can have n-groups. 

 

Here is some more example data for a better understanding:

Person*******Activite********Start******************End******************Location***********Group*****
Person 2Ward2019-06-24 14:572019-06-27 20:50Ward 1371
Person 2Ward2019-06-27 20:512019-06-28 20:32Ward 1371
Person 2Ward2019-06-28 20:332019-07-06 09:05Ward 671
Person 2Ward2019-07-06 09:062019-07-09 14:19Ward 1361
Person 2Ward2019-07-09 14:202019-07-16 13:07Ward 6031
Person 2Ward2019-11-01 13:052019-11-10 14:06Ward 3572
Person 2Ward2019-11-10 14:072019-11-10 15:10Ward 252
Person 2Ward2019-11-10 15:112019-11-12 16:33Ward 252
Person 2Ward2019-11-12 16:342019-11-20 16:01Ward 3572
Person 2Ward2019-11-20 16:022019-11-21 12:14Ward 252
Person 2IVA2019-11-21 12:152019-11-21 21:00IVA2
Person 2Ward2019-11-21 21:012019-11-22 13:14Ward 252
Person 2Ward2019-11-22 13:152019-11-27 13:53Ward 252
Person 2Ward2019-12-03 11:242019-12-03 16:44Ward 253
Person 2IVA2019-12-03 16:452019-12-03 22:36IVA3
Person 2Surgery2019-12-03 22:372019-12-03 23:26Surgery 13
Person 2Ward2019-12-03 23:272019-12-03 23:29Ward 1353
Person 2IVA2019-12-03 23:302019-12-04 13:10IVA 23
Person 2Ward2019-12-04 13:112019-12-12 14:22Ward 1353
Person 2Ward2019-12-12 14:232019-12-16 17:41Ward 1353
Person 2Ward2020-08-12 18:342020-08-13 01:08Ward 914
Person 2Ward2020-08-13 01:092020-08-13 12:30Ward 1374
Person 2Ward2020-08-13 12:312020-08-20 12:40Ward 104
      
Person 3Ward2019-05-23 19:572019-05-24 11:07Ward 1351
Person 3Surgery2019-05-24 11:082019-05-24 13:39Surgery 11
Person 3Post-op2019-05-24 13:402019-05-24 19:49Post-op 961
Person 3Ward2019-05-24 19:502019-05-26 19:20Ward 1351
      
Person 4Surgery2019-12-19 13:432019-12-19 14:04Surgery 11
Person 4Ward2019-12-19 14:052019-12-19 15:19Ward 1351
Person 4Post-op2019-12-19 15:202019-12-19 20:03Post-op 951
Person 4Ward2019-12-19 20:042019-12-21 15:47Ward 1351

 

Thank you so much in advanced a have a great weekend!

  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    ToreVingare - OK, this was far easier than I thought it would be. But, it is a 2 step process:

    Consequective = 
    VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) && [End]<EARLIER([Start])),[End])
RETURN
    IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. < .000695,1,0)


Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) && [Start] <= EARLIER([Start]) && [Consequective]=0))+1

    Those are columns. Attached a PBIX file below sig. Table (3). 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    ToreVingare - This is the second one of these I have seen now so I guess I am going to have to sit down and give this some serious thought.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    ToreVingare - OK, this was far easier than I thought it would be. But, it is a 2 step process:

    Consequective = 
    VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) && [End]<EARLIER([Start])),[End])
RETURN
    IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. < .000695,1,0)


Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) && [Start] <= EARLIER([Start]) && [Consequective]=0))+1

    Those are columns. Attached a PBIX file below sig. Table (3). 

    • ToreVingare's avatar
      ToreVingare
      Frequent Visitor
      5 years ago

      Wow! It works great! You saved my weekend! 🙏

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Greg_Deckler,

       

      Is there a way we achieve same in PowerQuery? If yes, can you update the same PBIX. I'm struggling to achieve it.

       

      Thank you so much in advance.