How many in the bar at 8am!?

[leon_m_holt]

Hi,

I have a query based on a sort of 'man walks into a bar' scenario, I only have basic skills (at best) with Alteryx so have been scratching my noggin for a few days trying to work this out, I'm hoping the Alteryx Community can save the day (and my sanity!)

My dataset gives me an arrival date & time and a leaving date & time but I need to establish how many people were 'in the bar' at 8am of each day, this is the simpler part of my query, the confusion comes when people leave 'the bar' a few days later (lets assume this is a 24/7 bar :P);

Name	Arrival Date	Arrival Time	Leaving Date	Leaving Time
Peter	2019-03-01	07:33:00	2019-03-01	14:20:00
Simon	2019-03-01	06:30:00	2019-03-02	12:33:00
Timothy	2019-03-01	08:50:00	2019-03-01	13:11:00
James	2019-03-01	00:17:00	2019-03-04	16:25:00
Jonathon	2019-03-01	08:00:00	2019-03-03	11:17:00
Arthur	2019-03-01	08:52:00	2019-03-01	13:10:00

So the desired outcome from this sample dataset would be along the lines of;

Date	People in the bar at 8am
2019-03-01	4
2019-03-02	3
2019-03-03	2
2019-03-04	1

I appreciate any help or guidance you can offer me.

Many thanks

Léon

[LordNeilLord]

Hey @leon_m_holt 

I won't give the exact answer but I would look for a tool that somehow generates new rows  in the for each minute for every customer

so for example you would end up with records for peter that look something like this:

2019-03-10 07:33:00

2019-03-10 07:34:00

2019-03-10 07:35:00

etc..

2019-03-10 14:20:00

Then you can filter on every day at 8am and count how many were in the pub

[CarlDi]

hey @leon_m_holt !

@LordNeilLord is spot on. The generate rows tool is the solution here. I've attached my solution. It may not be the most efficient since I'm traveling from a conference but it will get you close. Let me know if it helps.

[leon_m_holt · Accepted solution]

Thanks both,

The workflow example was a great help as I am unfamiliar with the 'Generate Rows' tool, I just made a little tweak to the 'Loop Expression' formula and added an extra column using the 'Formula' tool and was able to achieve the desired output, final workflow attached :)

Many thanks

Léon