Bed occupancy

[Qliksense_77]

Hi,

I have data like below from a hospital.

I want to be able to make a table showing how many patients that where in the hospital at a specific time and date.

Can anybody help?

DATA:
ID	Department	Arrival date	Arrival time	Departure date	Departure time
1	a	01-01-2022	08	03-01-2022	09
2	a	02-01-2022	08	03-01-2022	10

Desired output - number of patients:
Time	01-01-2022	02-01-2022	03-01-2022
00	0	1	2
01	0	1	2
02	0	1	2
03	0	1	2
04	0	1	2
05	0	1	2
06	0	1	2
07	0	1	2
08	1	2	2
09	1	2	2
10	1	2	0
11	1	2	0
12	1	2	0
13	1	2	0
14	1	2	0
15	1	2	0
16	1	2	0
17	1	2	0
18	1	2	0
19	1	2	0
20	1	2	0
21	1	2	0
22	1	2	0
23	1	2	0

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944

[Qliksense_77]

@MarcoWedel , thanks for info. 

I know how to use that method for dates. So I already know how to count how many patients where in the hospital at a specific date. But I dont know how to count how many patient where in the hospital at a specific time of a specific day.

Do you know how I can create both reference dates and reference hours at the same time?

[MarcoWedel · Accepted solution]

maybe one solution might be:

DATA:
LOAD ID,
     Department,
     Date#(ArrivalDate,'DD-MM-YYYY')	as ArrivalDate,
     Time#(ArrivalTime,'hh')	as ArrivalTime,
     Date#(DepartureDate,'DD-MM-YYYY')	as DepartureDate,
     Time#(DepartureTime,'hh')	as DepartureTime
Inline [ 	 	 	 	 
ID,	Department,	ArrivalDate,	ArrivalTime,	DepartureDate,	DepartureTime
1,	a,	01-01-2022,	08,	03-01-2022,	09
2,	a,	02-01-2022,	08,	03-01-2022,	10
3,	a,	02-01-2022,	16,	03-01-2022,	08
4,	a,	03-01-2022,	04,	04-01-2022,	12
5,	a,	03-01-2022,	14,	04-01-2022,	10
6,	a,	03-01-2022,	16,	05-01-2022,	10
];

tabLink:
LOAD ID,
     Timestamp#(Timestamp(ArrivalDateTime+(IterNo()-1)*'01:00:00','DD-MM-YYYY hh'),'DD-MM-YYYY hh') as DateTime
While Timestamp#(Timestamp(ArrivalDateTime+(IterNo()-1)*'01:00:00','DD-MM-YYYY hh'),'DD-MM-YYYY hh') <= DepartureDateTime;
LOAD ID,
     Timestamp#(ArrivalDate&' '&ArrivalTime,'DD-MM-YYYY hh') as ArrivalDateTime,
     Timestamp#(DepartureDate&' '&DepartureTime,'DD-MM-YYYY hh') as DepartureDateTime
Resident DATA;

tabDateTimeCalendar: 
LOAD *,
     Date(Floor(DateTime),'DD-MM-YYYY') as Date,
     Time(Frac(DateTime),'hh') as Time,
     Hour(DateTime) as Hour,  
     Day(DateTime) as Day,   
     WeekName(DateTime) as WeekName,   
     MonthName(DateTime) as MonthName,   
     QuarterName(DateTime) as QuarterName,  
     Year(DateTime) as Year;    
LOAD Timestamp#(Timestamp(MinDateTime+(IterNo()-1)*'01:00:00','DD-MM-YYYY hh'),'DD-MM-YYYY hh') as DateTime  
While Timestamp#(Timestamp(MinDateTime+(IterNo()-1)*'01:00:00','DD-MM-YYYY hh'),'DD-MM-YYYY hh') <= MaxDateTime;  
LOAD Min(DateTime) as MinDateTime,  
     Max(DateTime) as MaxDateTime  
Resident tabLink;

hope this helps

Marco

[Qliksense_77]

@MarcoWedel  very nice😉  I appreciate it😀

It does the job. I would appreciate if you could shortly explain how it works. I dont fully understand the steps.