Group Patient IDs by Consecutive Date Ranges

[#1]

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

[Gysbert_Wassenaar]

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

     ;

[Digvijay_Singh]

May be this -

[Digvijay_Singh]

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

[#5]

Thank you! Currently working on implementing this.

[#6]

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

[MarcoWedel]

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