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

[lzanotti]

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: 

snapdate	group	date	room	dept
01/02/2024	A	23/01/2024	F	HR
01/02/2024	A	24/01/2024	F	HR
01/02/2024	A	25/01/2024	F	OP
02/02/2024	A	23/01/2024	F	HR
02/02/2024	A	24/01/2024	F	HR
02/02/2024	A	25/01/2024	F	HR
02/02/2024	A	26/01/2024	F	OP
03/02/2024	A	23/01/2024	F	HR
03/02/2024	A	25/01/2024	F	HR
03/02/2024	A	26/01/2024	F	OP
04/02/2024	A	23/01/2024	F	HR
04/02/2024	A	26/01/2024	G	HR
04/02/2024	A	24/01/2024	F	OP
04/02/2024	A	27/01/2024	F	OP
05/02/2024	A	23/01/2024	F	HR
05/02/2024	A	26/01/2024	G	HR
05/02/2024	A	24/01/2024	F	OP
05/02/2024	A	27/01/2024	F	OP

 Outcome table

snapdate	group	date	room	dept	change?	whatchange?
02/02/2024	A	25/01/2024	F	HR	yes	dept
02/02/2024	A	26/01/2024	F	OP	yes	date
04/02/2024	A	26/01/2024	G	HR	yes	room and dept
04/02/2024	A	24/01/2024	F	OP	yes	date
04/02/2024	A	27/01/2024	F	OP	yes	addition

Any help/direction would be much appreciated. 

Thanks
Luca

[caltang]

Hey @lzanotti Luca,

Your dataset changes everytime and you are capturing snapshots of the changes. Before we proceed to this solution, may I ask, what is stopping yourself from changing the process? Maybe instead of overwriting previous entries, you can create new entries and take the latest entries as your data for investigations. I think that is better.

But if you are limited in your capacity to change that for whatever reason, that is also fine. It is always best to fix the problem at its source rather than its offshoots. I'd like to know a bit more about your request - and here are my queries:

1. The snapdate is when you get the data and the "date" is when that event happens, correct?
2. Do you have a matrix of all possible changes? In this case, I can see "room" and "dept" being easy to identify. But what about "date" and "addition" - what are those?

[caltang]

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

[lzanotti]

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]

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.