Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find the stops within the batches.

stj1120
8 - Asteroid

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.

4 REPLIES 4
danilang
19 - Altair
19 - Altair

Hi @stj1120 

 

In situations like this it helps with debugging to have the data sorted.  Here I've sorted by [Batchstart] and [StartStopDateTime] 

danilang_0-1626352982002.png

I also then use 3 formulas to calculate the various fields 

 

danilang_1-1626353059350.png

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

 

kunderwood
8 - Asteroid

Attached are my findings based on my understanding of the issue. I was unable to find any StopStart/StopEnd times that were outside of their respective Batch Number's BatchStart/End times. 

 

 

stj1120
8 - Asteroid

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.

danilang
19 - Altair
19 - Altair

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

Labels