Census Data

[timothyharlin]

Hello, I am trying to track number of patients at a hospital at any given time. I have arrival hour and departure hour and am trying to find a formula that will show all hours a patient was present.

For example, if a patient arrived at the hospital at 11am and left at 15:30 I would want to indicate that the patient was there at 11, 12, 13, 14 and 15. Any ideas would be greatly appreciated!

[IraWatt · Accepted solution]

Hey @timothyharlin,

I would do achieve this using the generate rows tool. Check my attached example

There is quite a good article on the generate rows tool here on the community: Tool Mastery | Generate Rows - Alteryx Community générer les lignes zeilen generieren 

Any questions or issues please ask! 
Hope that Helps

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Luke_C]

Hi @timothyharlin 

Sample data is always helpful, but hows something like this?

1. Generate rows to trim time to the hour and add a record for each hour between the in and out time
2. Since the trim goes to the beginning of the hour, a filter is used to remove records that are before the 'in' time. i.e. if it's 9:34 it produces a record for 9:00, but based on your example we may not want that, if you do just remove the filter
3. Formula to parse just the hour number

[timothyharlin]

I think this will work! One question though - do you have any suggestions on times that overlap to the next day? For example, a patient arrives at 23:00 but leaves at 7:00 the next morning.

[Matthew]

deleted (see my next comment)

[Matthew · Accepted solution]

this method should account for hours that roll over midnight

[Ben_H]

Hi @timothyharlin,

Why don't you just generate a column with the actual date as well?

So have a column with the hours and then one with the date (minus the time) that way you're always completely certain about what you're looking at.

Regards,

Ben

[Matthew]

@Ben_H  i'm completely guessing here, but i imagine that @timothyharlin needs each hour broken out so they can log activities. Then they could see if any blocked hours are missed.

edit: oh i see what you're saying.. have a column for date and a column for hour... i agree with this.. @timothyharlin this is a good idea.. I've adjusted my solution above to include the full timestamp for each hour.. it was much easier to do it that way.

[timothyharlin]

Thanks everyone for the help! I used a combination of a couple of solutions. New to Alteryx, so seeing how you all came up with solutions was crazy helpful.