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

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

Shivam
8 - Asteroid

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.

4 REPLIES 4
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

estherb47
15 - Aurora
15 - Aurora

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

Shivam
8 - Asteroid

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

fidelina22
5 - Atom

@EstherB47

@NicoleJohnson

 

Can you send this in a power query view or with the Power Query formulas that are needed?  I'm not able to decipher the information from the xml file.

 

fidelina22_0-1590607090512.png

 

Labels