Help: Create new rows for missing dates

[mzsweetumz]

Hello all,

This is my first time posting here! It is also my first time using Alteryx with my new role.

Here is some background information on the data I am trying to get: I am trying to generate a report of 'Occupied' or 'Available' hospital beds based off of the 'Bed ID' and the date (each day). 

Based off of my screenshot, I generated 'Occupied' column based off of the date duration between 'Admit Date' and 'Discharge' date. It created a new row for each date in between those dates which means those beds are occupied on those dates.

I am not sure if this is the right way to approach it. It might be easier to have each day whether available or not listed as their own row... and then one column that says bed status: occupied or available for each row.

Just not sure how I to apply the logic formula to produce that.

Occupied = All days on and between Admit Date and Discharge Date

Available = Every day that does not exist on or between Admit Date and Discharge Date, or the dates between the last Discharge Date and the new Admit Date.

Also want to point out that i am focusing on date/days only, and not the hours/times.

Any help is appreciated.

[atcodedog05]

Hi @mzsweetumz 

Can you provide this data as a excel file we will be happy to help : )

[mzsweetumz]

Thank you so much!! This is a sample data but any help with the workflow would be amazing!! 😀

[mzsweetumz]

Thank you so much! attached is the sample data... any help is appreciated 😀

[markcurry]

Hi @mzsweetumz 

Hopefully the attached workflow will point you in the right direction.

It does just work for full days, which won't be that accurate, so if a bed was freed up at 08:00 it is still down as being occupied for the full day.   Checking availability by the hour could be too much, so you could adjust the discharge times.  You could remove 8 or 10 hours from discharge time to allow for early morning discharges, so if the bed was freed up before 8am or 10am, it would then change the discharge date to the previous day, so for rest of the day the bed would be seen as available, unless occupied again.

[mzsweetumz]

Hi Mark,

Thank you for helping me with this! I found the workflow helpful... except I wanted the output to be something more like this:

I want each date to have it's own row and the bed status per bed ID.

Occupied = All days on and between Admit Date and Discharge Date

Available = Every day that does not exist on or between Admit Date and Discharge Date, or the dates between the last Discharge Date and the new Admit Date.

[markcurry · Accepted solution]

Hi @mzsweetumz  

See attached, hopefully that gives you what you're after....

[mzsweetumz]

Thank you so much Mark!! This was exactly what I needed. I will look at every step you took to understand the workflow. I appreciate your help!

[markcurry]

@mzsweetumz you're welcome, glad it worked.