Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I work in a hospital where we have medical wards that are only open on a certain number days of the week. These wards can change month to month. So what I have is the following table (Called WARDOPEN).
DateValid | Ward | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | TotalDaysOpen |
1/01/2012 | Medical1 | 1 | 1 | 1 | 3 | ||||
1/04/2014 | Medical1 | 1 | 1 | 1 | 1 | 4 | |||
1/07/2015 | Medical1 | 1 | 1 | 1 | 1 | 1 | 1 | 6 | |
1/01/2016 | Medical1 | 1 | 1 | 1 | 1 | 1 | 5 |
As you can see on each of these dates the number of days the ward was opened changed.
I then have a second table (Called BEDS), that has for each month how many beds are located in that ward as follows:
Month | Ward | Beds |
1/01/2015 | Medical1 | 28 |
1/02/2015 | Medical1 | 28 |
1/03/2015 | Medical1 | 28 |
1/04/2015 | Medical1 | 28 |
1/05/2015 | Medical1 | 28 |
1/06/2015 | Medical1 | 28 |
1/07/2015 | Medical1 | 28 |
1/08/2015 | Medical1 | 28 |
1/09/2015 | Medical1 | 28 |
1/10/2015 | Medical1 | 28 |
1/11/2015 | Medical1 | 28 |
1/12/2015 | Medical1 | 30 |
1/01/2016 | Medical1 | 30 |
1/02/2016 | Medical1 | 30 |
1/03/2016 | Medical1 | 31 |
1/04/2016 | Medical1 | 32 |
1/05/2016 | Medical1 | 33 |
1/06/2016 | Medical1 | 34 |
1/07/2016 | Medical1 | 35 |
I have a master calendar loaded so what I essentially need to do is to link the second table (BEDS) to the first one so it will look as follows:
Month | Ward | Beds | Total Open Days for week |
1/01/2015 | Medical1 | 28 | 4 |
1/02/2015 | Medical1 | 28 | 4 |
1/03/2015 | Medical1 | 28 | 4 |
1/04/2015 | Medical1 | 28 | 4 |
1/05/2015 | Medical1 | 28 | 4 |
1/06/2015 | Medical1 | 28 | 4 |
1/07/2015 | Medical1 | 28 | 6 |
1/08/2015 | Medical1 | 28 | 6 |
1/09/2015 | Medical1 | 28 | 6 |
1/10/2015 | Medical1 | 28 | 6 |
1/11/2015 | Medical1 | 28 | 6 |
1/12/2015 | Medical1 | 30 | 6 |
1/01/2016 | Medical1 | 30 | 5 |
1/02/2016 | Medical1 | 30 | 5 |
1/03/2016 | Medical1 | 31 | 5 |
1/04/2016 | Medical1 | 32 | 5 |
1/05/2016 | Medical1 | 33 | 5 |
1/06/2016 | Medical1 | 34 | 5 |
1/07/2016 | Medical1 | 35 | 5 |
Please help as I cant even get close to thinking how to achieve this.
Try Below script:
table2:
LOAD
Month&Ward as Key,* Inline
[
Month, Ward, Beds
1/01/2015, Medical1, 28
1/02/2015, Medical1, 28
1/03/2015, Medical1, 28
1/04/2015, Medical1, 28
1/05/2015, Medical1, 28
1/06/2015, Medical1, 28
1/07/2015, Medical1, 28
1/08/2015, Medical1, 28
1/09/2015, Medical1, 28
1/10/2015, Medical1, 28
1/11/2015, Medical1, 28
1/12/2015, Medical1, 30
1/01/2016, Medical1, 30
1/02/2016, Medical1, 30
1/03/2016, Medical1, 31
1/04/2016, Medical1, 32
1/05/2016, Medical1, 33
1/06/2016, Medical1, 34
1/07/2016, Medical1, 35
];
Left Join
LOAD DateValid&Ward as Key,TotalDaysOpen Inline
[
DateValid, Ward, TotalDaysOpen
1/01/2012, Medical1,3
1/01/2015, Medical1,4
1/07/2015, Medical1,6
1/01/2016, Medical1,5
];
NoConcatenate
Final:
LOAD if(IsNull(TotalDaysOpen),Peek(DaysOpen),TotalDaysOpen) as DaysOpen,
Month,Ward,Beds
Resident table2;
DROP Table table2;
HTH
Hi Sushil,
Thanks for the quick reply. Unfortunately this doesn't work. For example if I add a few more dates to the reporting month in the load script:
Beds:
Month | Ward | Beds |
01/03/2011 | Medical1 | 25 |
01/02/2011 | Medical1 | 25 |
01/03/2011 | Medical1 | 25 |
and I add another date to the Ward Open table
WardOpen:
DateValid | Ward | TotalDaysOpen |
1/01/2010 | Medical1 | 2 |
1/01/2012 | Medical1 | 3 |
1/01/2015 | Medical1 | 4 |
1/07/2015 | Medical1 | 6 |
1/01/2016 | Medical1 | 5 |
Then in theory I want any dates that fall in the Beds table to look at the ward open table and where that date falls between that date then it will pick up the days open in that range.
For example: 01/03/2011 from the Beds table should pick up total days open as 2 as this is greater than the 01/01/2010 and less than the 01/01/2012 in the WardOpen table when the next days open was changed.
and if the date was 01/03/2015 from the Beds table then I want it to pick up total days open as 4 as this is greater than the 01/01/2015 and less than the 01/07/2015 in the WardOpen table. etc etc.
When I add these extra dates to the inline script it just gives blank dates.
Hi,
Try this code:
Temp:
LOAD DateValid,TotalDaysOpen,Ward Inline
[
DateValid, Ward, TotalDaysOpen
1/01/2012, Medical1,3
1/01/2015, Medical1,4
1/07/2015, Medical1,6
1/01/2016, Medical1,5
];
let vmindate = Num(Date#(Peek('DateValid',0,'Temp'),'M/DD/YYYY'));
let vmaxdate = Num(Date#(Peek('DateValid',NoOfRows('Temp')-1,'Temp'),'M/DD/YYYY'));
Temp2:
LOAD Date($(vmindate)+IterNo()-1,'M/DD/YYYY') as Date
AutoGenerate(1)
While $(vmindate)+IterNo()-1<=$(vmaxdate);
Left join (Temp2)
LOAD DateValid as Date, TotalDaysOpen,Ward
Resident Temp;
DROP Table Temp;
NoConcatenate
Final:
LOAD if(IsNull(TotalDaysOpen),Peek(DaysOpen),TotalDaysOpen) as DaysOpen,
Date
Resident Temp2;
DROP Table Temp2;
//EXIT SCRIPT;
Inner join (Final)
LOAD
Date,Beds,Ward Inline
[
Date, Ward, Beds
1/01/2015, Medical1, 28
1/02/2015, Medical1, 28
1/03/2015, Medical1, 28
1/04/2015, Medical1, 28
1/05/2015, Medical1, 28
1/06/2015, Medical1, 28
1/07/2015, Medical1, 28
1/08/2015, Medical1, 28
1/09/2015, Medical1, 28
1/10/2015, Medical1, 28
1/11/2015, Medical1, 28
1/12/2015, Medical1, 30
1/01/2016, Medical1, 30
1/02/2016, Medical1, 30
1/03/2016, Medical1, 31
1/04/2016, Medical1, 32
1/05/2016, Medical1, 33
1/06/2016, Medical1, 34
1/07/2016, Medical1, 35
];
HTH
Join Your BEDS and WARDS TABLE with a YearMonth Field.
Relate MasterCalendar.YearMonth to your BedsTable.YearMonth
BEDS:
LOAD Month,
subfield(Month,'/',3) & subfield(Month,'/',2) as YearMonth,
Ward,
Beds
FROM
C:\Users\vp51284\Documents\CommunityHelp\Beds.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
OUTER JOIN(BEDS)
WARDS:
LOAD DateValid,
subfield(DateValid,'/',3) & subfield(DateValid,'/',2) as YearMonth,
sum(TotalDaysOpen) as TotalDaysOpen
FROM
C:\Users\vp51284\Documents\CommunityHelp\HospWard.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
Group BY DateValid;
MinMax:
Load
MIN(DateValid) as MinDate,
MAX(DateValid) as MaxDate
FROM
C:\Users\vp51284\Documents\CommunityHelp\HospWard.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
//Assign min and max dates to variables
Let vMinDate = NUM(Peek('MinDate',0,'MinMax'));
Let vMaxDate = NUM(Peek('MaxDate',0,'MinMax'));
Let vToday = num(Date(Now()));
//Creating the TEMP calendar, populating missing dates
TempCal:
LOAD
DATE($(vMinDate)+RowNo()-1) as TempDate
Autogenerate
$(vMaxDate)-$(vMinDate)+1;
MasterCalendar:
LOAD
TempDate as Date,
AutoNumber(TempDate) as DateNum,
'FY-' & Year(TempDate) as Year,
NUM(YEAR(TempDate)&num(Month(TempDate),'00'),'0') as YearMonth
RESIDENT TempCal
Order By TempDate ASC;
Thanks guys really helps. Really appreciate your help.
Hey guys, I finally got around to putting this into an application and still doesn't quite work right for me. If you use the one example you have given it works prefect but if you had a whole bunch of different wards to the bed file and a handful of different wards to the ward open day file it doesn't quite calculate as needed. Any help would be great.