topic Re: Group Patient IDs by Consecutive Date Ranges in QlikView

Group Patient IDs by Consecutive Date Ranges

Wed, 03 May 2017 16:38:30 GMT

In the table below patient 001 has consecutive date ranges in the first three rows, so that should count as a single hospital admission (for which I would calculate a single total Length of Stay (LOS)=41). Rows 4 and 5 should be counted as a separate admission (another two consecutive date ranges), total LOS = 20 days. Person 002 in the last row has only one date range, which is counted as one admission with a LOS of 8.

ID	START	END	LOS
001	2016-08-24	2016-08-31	7
001	2016-09-01	2016-09-30	29
001	2016-10-01	2016-10-06	5
001	2016-12-03	2016-12-19	16
001	2016-12-20	2016-12-24	4
002	2016-02-12	2016-02-20	8

Re: Group Patient IDs by Consecutive Date Ranges

Gysbert_Wassenaar — Wed, 03 May 2017 18:01:18 GMT

Perhaps something like this:

tempTable:

LOAD

ID,

START,

END,

LOS

FROM

source_table

;

/* If START and END are text fields then first convert them to dates using the Date#() function:

Date(Date#(START, 'YYYY-MM-DD'),'YYYY-MM-DD') as START, */

Result:

LOAD

AutoNumber([Admission START], ID) as [Admission Number]

;

LOAD

If(START = Previous(END) + 1, Peek('Admission START'), START) as [Admission START]

If(START = Previous(END) + 1, LOS + Peek('Running Admission LOS'), LOS) as [Running Admission LOS]

RESIDENT

tempTable

ORDER BY

ID,

START,

END

;

Re: Group Patient IDs by Consecutive Date Ranges

Digvijay_Singh — Wed, 03 May 2017 18:20:33 GMT

May be this -

Re: Group Patient IDs by Consecutive Date Ranges

Digvijay_Singh — Wed, 03 May 2017 18:38:14 GMT

This is far better than I proposed! I think ID check would be needed along with START date comparison, am I missing something?

Re: Group Patient IDs by Consecutive Date Ranges

Wed, 03 May 2017 19:17:04 GMT

Thank you! Currently working on implementing this.

Re: Group Patient IDs by Consecutive Date Ranges

Wed, 03 May 2017 19:17:51 GMT

Thank you! I am currently trying to implement both proposed solutions.

Re: Group Patient IDs by Consecutive Date Ranges

MarcoWedel — Wed, 03 May 2017 21:58:07 GMT

Hi,

another solution might be:

table1:
LOAD ID,
    START,
    END,
    END-START as LOS,
    RecNo() as %Key
FROM [https://community.qlik.com/thread/259171] (html, codepage is 1252, embedded labels, table is @1);
Join
LOAD %Key,
    RangeSum(Peek(admID),-(ID<>Previous(ID) or START<>Previous(END)+1)) as admID
Resident table1
Order By ID, START;

hope this helps

regards

Marco