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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

How many in the bar at 8am!?

Meteoroid

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);

 

NameArrival DateArrival TimeLeaving DateLeaving Time
Peter2019-03-0107:33:002019-03-0114:20:00
Simon2019-03-0106:30:002019-03-0212:33:00
Timothy2019-03-0108:50:002019-03-0113:11:00
James

2019-03-01

00:17:002019-03-0416:25:00
Jonathon

2019-03-01

08:00:002019-03-0311:17:00
Arthur2019-03-0108:52:002019-03-0113:10:00

 

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

 

DatePeople in the bar at 8am
2019-03-014
2019-03-023
2019-03-032
2019-03-041

 

I appreciate any help or guidance you can offer me.

 

Many thanks

 

Léon

Alteryx Certified Partner

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

Highlighted
Alteryx
Alteryx

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.

Meteoroid

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

 

Labels