Finding new and removed entries in different versions of list defined by date

[andreasledet]

Hi there,

I have a feeling this will either be very challenging or very easy... But I have had no luck trying different ideas and can't seem to find a question like this on this forum. 

So here goes: 

I have a database with a number of different lists of the same type and layout. This database is updated daily with new versions of the lists appended to the bottom. 

What I'm trying to do is generate a table that shows new entries or removed entries on these lists. 

Here's an example of the database (the real one has between 50 and 100 entries on each list version, with 9 lists in total - so about 6-700 rows per day):

List name	Position	Name	Date
Berlin	1	John	2018-11-10
Berlin	2	Jack	2018-11-10
Berlin	3	Anna	2018-11-10
Berlin	4	Lena	2018-11-10
London	1	Cole	2018-11-10
London	2	Julia	2018-11-10
London	3	Dean	2018-11-10
Berlin	1	John	2018-11-11
Berlin	2	Fred	2018-11-11
Berlin	3	Anna	2018-11-11
Berlin	4	Lena	2018-11-11
London	1	Cole	2018-11-11
London	2	Julia	2018-11-11
London	3	Dean	2018-11-11
London	4	Tina	2018-11-11
London	5	Eric	2018-11-11

So for this one, between the two dates, Jack is removed from the Berlin list while Fred is added, and Tina and Eric are added to the London list. 

I imagine the output could look something like this - but not necessarily exactly like this:

Action	Name	Date	List
Remove	Jack	2018-11-11	Berlin
Add	Fred	2018-11-11	Berlin
Add	Tina	2018-11-11	London
Add	Eric	2018-11-11	London

The length of the lists vary, as well as the frequency of how often they change. Some change daily, some weekly, some hardly ever change. But there is a version of each list for each date, even if there was no change to a list.

Thanks in advance!

[jdunkerley79]

I'm probably over complicating this but I would do the following.

Make a set on unique days

Sort this and produce a new column of PrevDate using a MultiRow tool

Add Prev Date to the original data set

Join the data set to itself with PrevDate = Date 

The Left output will be Adds, the Right output will be Removes.

A little additional tidying to remove the first and last dates where additional data appears

[yalmar_m]

Hi @andreasledet,

This might help, and maybe it's less complicated that @jdunkerley79's solution!

Best,

Yalmar

[jdunkerley79 · Accepted solution]

@yalmar_m, you're right I over complicated it.

- Again get set of dates

- Compute Prev and Next

- Join to original Set

- Use a multi-row to see when Prev/Next does not equal previous or next within each Item Name and Name and compute Add/Remove

This deals with first and last rows for free as well

[andreasledet]

This was exactly what I needed. Thank you!