numbering care episodes

[#1]

Hi,

I have a database with ID and Date_F. The variable "Date_F" is the date in which a health care service was delivered. I need to identify and number the care episodes for each patient.  The delivery of a service is part of a new care episode if the difference between the date of the service delivery and the previous one are 90 or more days. 

I attach an EXCEL file with two sheets (I have qlik personal edition). The first "DB" contains data of four patients of the two variables ID and DATE_F. The second "Peek table" represents an attempt to resolve the problem. It also contains the variable inserted manually with the desired outcome.

Thanks for any help

Daniel

[lironbaram · Accepted solution]

hi

run this script

it should get you the result you look for

datatemp:

LOAD ID,

     DATE_F

FROM

(biff, embedded labels, table is Data$);

Data1:

load ID,

     DATE_F,

     if(Previous( ID)=ID,peek(DATE_F)) AS DATE_S

Resident datatemp

order by ID,DATE_F;

drop Table datatemp;

Data2:

load *,

     DATE_F - DATE_S AS DAYS

Resident Data1;

drop Table Data1;

Data:

load *,

     if(Previous(ID)=ID,if(DAYS>=90,RangeSum(peek('Counter'),1),peek('Counter')),1) AS Counter

Resident Data2

order by ID, DATE_F;

drop Table Data2;

[#3]

Great!

Thanks Liron!