community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Split Total minutes (In and out Time) in 15-minute Time Frame

Meteor

Hi All,

 

I have four columns Date, In-time, Out-time, and Total Minutes. Further, I would like to create 15-min time frame columns and divide total minutes in those 15-minute time frame columns. I'd highly appreciate if you could suggest on how to achieve that?

 

 Annotation 2019-07-25 140837.png

 

I have attached the excel file of the image.

 

Thanks.

Magnetar
Magnetar

Fun one! This one can be done with a few key tools: Generate Rows (to find each minute between In & Out), a Formula to determine which "Time Bucket" each minute belongs to, and then Summarize tool to group by Time Buckets and count the number of minutes in each. Also included some functionality to get you the whole list of possible time buckets (used the same start and finish as your example data, but could easily be adjusted). 

 

SplitMinutes.JPG

 

Hope this helps! 

 

Cheers,

NJ

Pulsar

Hi @Shivam 

 

Here's another approach to your challenge. I reverted the Excel file back to the original 4 columns you had mentioned (first Select tool), and then used a formula tool to generate the first time grouping. A Generate Rows tool generates the 15 minute increments into the times you need into a field called [TimeStamp] (I just based this on the times in the Excel file).

 

Then, a Formula tool does the heavy lifting. If the [In Time] is greater than the [TimeStamp], then there's nothing to calculate. Otherwise, if the [TimeStamp] is less than the [Out Time] which captures when the total minutes should start, calculate the difference between [TimeStamp] and [In Time] in minutes. Otherwise, subtract the difference between [TimeStamp] and [Out Date] from 15. image.png

Zeros where the time grouping is before the In Time, calculated time difference when it's in range, and negative numbers where the time grouping is after both the In Time and Out Time.

A little more cleanup within the tool, recalculating the Minutes so that if they are greater than 15, they become 15 (the largest allowable interval), and replacing negative numbers with zero.

Next, use a DateTime tool to convert the TimeStamp (which will be used as columns in the crosstab) to text, removing the date piece.

Crosstab everything so that the intervals create the columns. And Dynamic Rename to replace the _ with :

 

image.png
Let me know if this helps.

 

Cheers!

Esther

Meteor

Thank you @EstherB47  and @NicoleJohnson.  Both the solutions work perfectly for me!

Labels