Census Data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sample data is always helpful, but hows something like this?
- Generate rows to trim time to the hour and add a record for each hour between the in and out time
- 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
- Formula to parse just the hour number
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
deleted (see my next comment)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.