Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Great!
Thanks Liron!