This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am looking for the most efficient way to accomplish the following ...
I have two tables. One that stores time ranges indicating production downtime, and one that stores incremental units produced as recorded at various timestamps.
For each record in the Downtime table, I want to capture the min and max values of the records in the Production table where the Production timestamp falls within the Start Time and End Time. This is so I can determine the number of units produced during the downtime period (Max Units - Min Units)
For reference, the Downtime table would typically have about 100 entries per day and the Production table would have 40-50,000 records per day. I can see this workflow being run for date ranges up to a full year, so there could be a massive amount of records to process.
I am also open to other suggestions on how to achieve the same results.
The first idea that comes to mind to solve this would be to first use an Append Fields tool then a Filter to keep only the records where the production time falls within each of the start/end times. From there, you just have to Summarize and use a Formula to calculate the difference. With so many records in the production table, though, the Append Fields tool may take a while. Try this out and let us know how it works for you.
TIP: Make sure you change the drop down at the bottom of the Append Fields tool to "Allow All Appends."
I would solve this problem using iteration macro as there are some limitation with appending data using append tool. You just need update max of number of iteration field of interface designer with count of records there in production table for macro everytime when you run this workflow
I find that the Generate Rows tool can be less frustrating than Append, especially when you're appending so many records. Perhaps it's faster too (I haven't tested)
You can use Generate Rows to create entries for all of the times between the ranges in your Downtime table. Then, join to the Production table, Summarize for Max and Min, and Formula to calculate the difference.