Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Calculate seconds within specific interval with start and stop time

Highlighted
7 - Meteor

Hi all!

 

I have certain data (see three example rows below) with a column "start(time)" and a column "stop(time)". 

I would like to calculate the seconds within every hour between 08:00:00 and 24:00:00 (or 00:00:00). 

EstherHesseling_3-1591115984839.png

 

I know that I am working very inefficient on this workflow as I am using many (many many formula tools.

The first formula I use:EstherHesseling_2-1591115884845.png

The second formula I use

EstherHesseling_4-1591116065136.png

 

The third formula:

EstherHesseling_5-1591116100447.png

 

The fourth formula: 

EstherHesseling_6-1591116133156.png

 

Because I need to do this for all of the other hours, this is not really a nice way. 

Furthermore, I don't know how to incorporate the stop time.

 

Could you please support me with this?

Highlighted
12 - Quasar

Try the Generate Rows tool.  See if the attached workflow helps.

 

Annotation 2020-06-02 160210.png

 

Chris

Highlighted
Alteryx Certified Partner

Hi @EstherHesseling ,

 

I've built the workflow as attached. This will calculate all seconds between those times.

 

You just need to append text fields as follows:

mceleavey_1-1591132268554.png

Then you can build the formula as below:

 

 

mceleavey_0-1591132251963.png

This gives the following results:

 

mceleavey_2-1591132375456.png

 

 

Hope this helps.

 

M.

 

Highlighted
11 - Bolide

Hi @EstherHesseling 

 

Is this what you are looking for:

 

Start_New3End_New37-88-99-1010-1111-1212-1313-1414-1515-1616-1717-18
08:04:4613:38:5303314360036003600360023330000
14:58:1817:35:540000000102360036002154
07:47:1915:42:18761360036003600360036003600360253800
Highlighted
7 - Meteor

hi @mceleavey 

 

I have tried the formula and it is almost right. I still need to incorporate the stop time, as it still counts 3600 seconds when the stop time has passed.

So if the stop time is 15:35:21, then it still counts 3600 seconds between 16:00:00 and 17:00:00.

 

Could you help me with this?

Highlighted
7 - Meteor

Hi @deviseetharaman,

 

Yes indeed, this is exactly what I am trying to do!

Highlighted
12 - Quasar

Take the workflow I posted above and add a formula to determine the Hour, then a Summarize tool..  Then you'll just need to work on the headers.

 

Annotation 2020-06-03 070024.png

 

 

Chris

Highlighted
7 - Meteor

Hi @ChrisTX 

I will try this, thanks!

I will let you know.

Highlighted
Alteryx Certified Partner

HI @EstherHesseling ,

 

apologies for the delay, the day job gets in the way a bit!

I've updated the formulae and attached the workflow. This should work for all calculations now.

 

M.

Highlighted
7 - Meteor

@ChrisTX  Thank you for the solution. 

I used your solution for a part of the workflow, for the rows with startday and stopday not being equal (for instance Friday respectively Saturday). As it generated a lot of rows and I had a total of 400K, it was really helpful for a part of the data!

 

@mceleavey This formula worked out, thanks a lot.

I used this solution for the other part of the workflow, for the rows with an equal start and stopday. This kept my workflow running relatively fast.

 

I am really happy I got the result I needed, thank you!!

Labels