Arcade or Python: Iterate through Related Table for Consecutive Values for Each GUID?

[ArmstKP]

We are looking to find the duration of time something was closed.  We have a status (open/closed) field and a datetime field.  How would I iterate through the related table for each GUID to find the amount of time something was closed?  I am guessing part of this would be to sort by date and GUID and then a DateDiff?

example data for one guid/relfeatureglobalid:

[DavidPike · Accepted solution]

I think the simplest way is actually using field calculator.  As you say, you first need to sort your data first by GUID, then  by datetime DESCENDING.  you need to physically reorder the table using https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/sort.htm

sorting just the attribute table view and running field calculator won't work as the internal cursor is by ObjectID (I believe)

#imports
import datetime

#create variables to store the global id and datetime
#records from the previous iteration
prev_dtime=''
prev_gid=''

#your datetime field, your globalid field
def my_func(dtime, gid):
  #make your globals
  global prev_dtime
  global prev_gid

  try:
    #if global ids match
    if gid == prev_gid:  
      difference = dtime - prev_dtime  
      #hours difference from previous record (sorted desc so it's actually 
      #the next record) 
      return float(difference.total_seconds()/3600)
  except: 
    #return whatever you like if error etc 
    return -999
  finally:
    #update the prev dtime and gid for the next iteration
    prev_dtime = dtime  
    prev_gid = gid

After you run this (obviously create a new float field to calculate into) you could also then summarize by GUID attribute and get the totals (definition query or filter out any 'open' values first e.g. 'FIELD LIKE 'Open')

Might get you started anyway

[ArmstKP]

@DavidPike Thank you for the example.  I am starting to tackle it.  I have tried to sort the data, but the Sort in Data Management doesn't allow one to sort by a GUID field?

[DavidPike]

Hmmmm, possibly creat a new id field (Text/String) and field calculate the GUID into it.

[ArmstKP]

@DavidPike Ok.  Here is what I've got.  Here's the table I'm working with:

Below is the field calculator:

Here is the error I am receiving:

Below is the code in the field calculator:

#imports
import datetime

#create variables to store the global id and datetime
#records from the previous iteration
prev_inspection_date=''
prev_guid=''

#your datetime field, your globalid field
def my_func(inspection_date, guid):
#make your globals
global prev_inspection_date
global prev_guid

try:
#if global ids match
if guid == prev_guid:
difference = inspection_date - prev_dtime
#hours difference from previous record (sorted desc so it's actually
#the next record)
return float(difference.total_seconds()/3600)
except:
#return whatever you like if error etc
return -999
finally:
#update the prev dtime and gid for the next iteration
prev_inspection_date = inspection_date
prev_guid = guid

[DavidPike]

you need to add your 2 arguments into my_func() above the code block - duration = ...

my_func(!inspection_date!, !guid!)

[ArmstKP]

@DavidPike Oops.  A little oversight on my part.  I ran the code and it worked very well.  A little manual work to remove a few entries, but I can create a filter in the code to accomplish that next time.  I appreciate your help on this very much!

[DavidPike]

Out of interest what were the entries needing removed?  The most recent entry for each GUID would be set to 0, but additional logic could be added to do a DateDiff with date.now(), although the time period would only really make sense at the time of computation.

[ArmstKP]

@DavidPike I needed to manually delete some values because sometimes our crews will submit multiple consecutive "open" statuses or multiple consecutive "closed" statuses with different datetimes....