track changes within snapshots addition/deletion/update in a group

Question

Hi Community,

I am facing an interesting challenge and I lost a bit of hope. 
I have a snapshot table collected daily of rooms booked an this can be amended/deleted/inserted every day. 
I have multiple groups but the date is unique to that group (no duplication within the group).  
My aim is to track the changes from the one snapshot to the next (or current and previous).

An example below . Ideally when you have a deletion and an addition it means the date has changed and I would like to be able to back-track this too:

snapdategroupdateroomdept01/02/2024A23/01/2024FHR01/02/2024A24/01/2024FHR01/02/2024A25/01/2024FOP02/02/2024A23/01/2024FHR02/02/2024A24/01/2024FHR02/02/2024A25/01/2024FHR02/02/2024A26/01/2024FOP03/02/2024A23/01/2024FHR03/02/2024A25/01/2024FHR03/02/2024A26/01/2024FOP04/02/2024A23/01/2024FHR04/02/2024A26/01/2024GHR04/02/2024A24/01/2024FOP04/02/2024A27/01/2024FOP05/02/2024A23/01/2024FHR05/02/2024A26/01/2024GHR05/02/2024A24/01/2024FOP05/02/2024A27/01/2024FOP

Outcome table

snapdategroupdateroomdeptchange?whatchange?02/02/2024A25/01/2024FHRyesdept02/02/2024A26/01/2024FOPyesdate04/02/2024A26/01/2024GHRyesroom and dept04/02/2024A24/01/2024FOPyesdate04/02/2024A27/01/2024FOPyesaddition

Any help/direction would be much appreciated.

Thanks
Luca

caltang · Answer

To identify a change in "date" itself can lead to multiple copies. We will need to anchor it to something Luca, otherwise the number of rows in the output will be more than your expected output.

Maybe you can provide more explanation on that bit? I'm still trying to tie my head around it. It involves the Multi-Row tool to get what you want, but we need to group it accordingly.

lzanotti · Answer

Hi @caltang ,

many thanks for the quick reply and looking into it. 
I totally get what you mean and I cannot change the source logic unfortunately.

Answers to your points: 
1)correct on snapdate but instead the field "date" represent the date when the room is scheduled and this may change as well. i.e. : on the 04/04/2024 you can see that one date has been swapped and one has been added. To be fair the "snapdate" should be used to identify when the even happened. 
2)changes can happen only on room/date/dept, the Outcome table is the final product which track all the changes/addition/deletion happened looking at the previous snapdate.

caltang · Answer

I've done some sample work here for you @lzanotti - if you can clarify the above points, then we can finish the build.

Example for Luca.yxmd