Calculate the first instance the status was true for the specific ID taking into account

[RoxanneR]

Hi, please help! I have input files containing data for specific month and containing information about the ID, Status which can be true or false and time period containing the months. Status is changing for the IDs throughout time. I need to calculate the first instance the status was true for the specific ID taking into account 3 months time period and display it.

Example:

| ID | Status | Timestamp |

|----------|--------|-------------------|

| ID_1 | True | 2023-01-05 08:00 |

| ID_1 | False | 2023-02-10 15:30 |

| ID_1 | True | 2023-03-20 12:45 |

| ID_2 | False | 2023-01-02 11:15 |

| ID_2 | True | 2023-02-18 09:45 |

| ID_2 | True | 2023-03-25 14:20 |

| ID_3 | True | 2023-01-08 13:30 |

| ID_3 | True | 2023-02-15 17:10 |

| ID_3 | False | 2023-03-10 10:00 |

Next step would be to display the time status was True before it turned to false, it should be displayed in the new column

[RoxanneR]

Hi @davidskaife 

Each month is one record, and by the logic within a general ID are multiple IDs with different statuses which might be changing month over month.

But the scenario I am trying to achieve if the reset of the Time Diff to 0. after the status change for a specific ID, so if there is status = "True" for 2 months it should be logged as 60-61 days depending on the months but once there is the status change to "False" (for that ID) and then "True" again at some point then the Time Diff. should "reset" and start the count from the newest True status, so from 0 and on ongoing basis.

I hope what I am saying makes sense..

[davidskaife]

Hi @RoxanneR 

Happy New Year!

So, i've had another look at this for you and please find attached a revised workflow that should work given the additional detail above.

Firstly the Tile tool will place each True instance into its own 'bucket', and sequentially do this for each ID (if you add in other ID's simply tick these on the Group By Columns box) - this identifies the unique runs of True and False

We filter out the False statuses, and summerise on ID and Tile_Num (again if you add in other ID's simply add this in as a group by option)  to find the first and last timestamps for each group of True statuses

We then calculate the difference in days, join the data back to the original and sort. The difference in days will be attached to the last True instance of that particular group.

If there is only one True instance, it returns 0

Hope this is what you're looking for!

[RoxanneR]

Hi @davidskaife ,

happy New Year to you as well.

I implemented your solution and one step didn’t work for me- as I join values on the with the join tool as suggested I get the Timestamp value which is the Min_timestamp instead of Max_timestamp. I don’t really understand how it’s possible since I configured everything as per your example. Should I add it manually somewhere?

thanks,

Roxanne

[davidskaife]

Hi @RoxanneR 

Do you mind sharing a screenshot of your Join tool settings?

[RoxanneR]

Hi, the solution worked for me after all. May I ask why don't we group by Timestamp in Tile tool as well, only by ID(s)?

Thanks!

[davidskaife]

Hi @RoxanneR 

I've used the Tile tool to obtain the groupings for each ID/status combo, so for every instance of a True per ID we get a sequential number assigned to it - 1st instance of True, second instance of True and so forth.

If we also grouped by timestamp then the instance would be 1 for everything, as all the timestamps are different

Hope that helps, and glad it works for you :)