Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
@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?
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
@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.