How to track new retailers and closed retailers from Month to Month?

[dsemitek]

Every month I get an Excel file of retail locations.  The retailer is always the same name, the Excel file is always formatted the same, and each retailer has a Unique ID.  I want to join the previous month to the current month but flag when a new retail location opens and when a retail location closed.  I would like to do this month over month to create a master retail list showing all historical locations: when they opened and when they closed.

I've been working on a join and then flagging the left and right joins as either "Opened Month/Year" and "Closed Month/Year".  This works great for 2 months but not sustainable when I have files that go back several years.  Manually recreating the workflow from the screenshot was taking a very long time.  Also, I had to manually use a formula for the current month and year which added to the time to create a huge workflow.  I tried to pull in the file name from the Input and then trim it to the month and year but that seemed to compound the problem when trying to join the end workflow to a new month.  The "Opened" month/year didn't get populated after subsequent Unions.

Here is the closest I've gotten but can't figure out how to loop the flow to start from the beginning and work through all of the files.  Ideally, I want the end of the workflow, after the Union, to go back up to the Left Input and have the Right Input change to the next month's file.

[CoG]

Can you share some sample data so that we can see what structure your data has? Macros would be the way to replicate the "looping" behavior you mentioned, but there may be a much easier way to accomplish behavior, using one input tool to pull in all files (since they have the same schema) and the Summarize Tool to identify start and end dates.

[dsemitek]

Hi CoC

The data is in a standard format: unique ID, retailer name, address, city, latitude, longitude, etc.  The files do not contain the date of the file in the attribute data; however, it is in the file name which follows the same format from file to file.

Thanks for the idea of the Summarize Tool.  If I can pull in each file using the Directory Input and appending the "Output File Name as Field" it just might work.  I would just need to determine the first time the location appears in the files, so maybe somehow taking the oldest date through a Filter or Join.  If the retailer stays open, then the location will be repeated in subsequent files. If the location closes, then it would no longer appear in the files.