Link dates from one date change to the next - Qlik Community

Report Inappropriate Content · ‎2016-07-04

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.

sushil353 · ‎2016-07-05

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

Report Inappropriate Content · ‎2016-07-05

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.

sushil353 · ‎2016-07-05

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

vinieme12 · ‎2016-07-05

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Report Inappropriate Content · ‎2016-07-06

Thanks guys really helps. Really appreciate your help.

Report Inappropriate Content · ‎2016-09-11

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.