Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksense_77
Creator
Creator

Bed occupancy

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
Labels (1)
  • Chart

1 Solution

Accepted Solutions
MarcoWedel

maybe one solution might be:

MarcoWedel_0-1656013164308.png

MarcoWedel_1-1656013225201.png

 

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

 

 

View solution in original post

4 Replies
Qliksense_77
Creator
Creator
Author

@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

maybe one solution might be:

MarcoWedel_0-1656013164308.png

MarcoWedel_1-1656013225201.png

 

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
Creator
Creator
Author

@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.