Hi all,
I have a data set where it has Batch Numbers, BatchStart Time and BatchStop Time.
Also it has the Stops which means that there will be an off at sometimes while the production is running due to some cause.
Therefore, to know when the Stops start and end we have StopStartDateTime and StopEndDateTime columns. Actualy the stops should lie within the batch. Also any stop time after the batch has ended should not be included. Same goes for stop time before the batch has started.But, that is not the case here...
Problem: I see lots of overlap in the Stop times. Most of previous batch stops are ending after the new batch start.
The time variance for the stops in each batch is varying which is like in one batch if it has the variance in seconds and some batches there is a variance in minutes, it is confusing for us to write the exact conditional logic to get the data of the stops within the actual batch.
I could not get the actual Stop start and Stop end time for each batch in Alteryx
So, from this data set I'd need to find the following:
1. Find all stops that overlaps before and after start of the batch. Also find before and after end of the batch.
2. Take only the stops that are lying within the same batch.
3. Calculate how much of each stop is actually inside the batch (if it only overlaps partly)
4. Note that a stop may overlap with more than one batch.
I tried using the below formula for finding the stop overlaps.
(StopStartDateTime < BatchStop and StopEndDateTime > BatchStart)
But, don't know if this condition is correct. I don't know how to do this and so, seeking for your help.
Please check and provide your suggestions or answers.
Thank you.
Best Regards,
Teja.
Solved! Go to Solution.
Hi @stj1120
In situations like this it helps with debugging to have the data sorted. Here I've sorted by [Batchstart] and [StartStopDateTime]
I also then use 3 formulas to calculate the various fields
The first 2 calculate if there's overlap between the batch start/end and the various stop records. The third one figures out the overlap. If it's one of the start/end events then it returns the overlap in seconds. If the stop event is completely within the batch it returns the duration of the stop event
Dan
Hi @danilang
Thank you for your quick response.
How to get only the stops and their durations that are lying within the batch?
Could you please help?
Thank you.
Best,
Teja.
Hi @stj1120
Starting from the workflow I provided, use a filter tool and look for the rows where [BeforeStart] and [AfterEnd] are both false.
Dan