Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Flagging each visit by calculating date range

I have a table with visit summary of patients visiting the hospital. This table contains the VisitID, PatientID, DateAdmitted, DateDischarged. I need to identify visits which were readmissions (i.e. visits which happened within a gap of 30 days from the previous visit). I need to add two columns flagging the visit as a readmission using the DateAdmitted and DateDischarged as Readmit_flag (a visit from a patient who was admitted in the hospital less than 30 days ago) and PrevAdmit_flag (an admission which lead to a readmission).

Dummy Data:

VisitIDPatientIDDateAdmittedDateDischarge
AA1PA15/26/20166/10/2016
AA2PA26/7/20166/13/2016
AA3PA35/30/20166/5/2016
AA4PA17/1/20167/5/2016
AA5PA27/1/20167/7/2016
AA6PA37/3/20167/7/2016
AA7

PA4

8/3/20168/6/2016
AA8PA58/4/20168/9/2016
AA9PA49/4/20169/8/2016

 

Output:

VisitIDPatientIDDateAdmittedDateDischargeReadmit_FlagPrevAdmit_flag
AA1PA15/26/20166/10/201601
AA2PA26/7/20166/13/201601
AA3PA35/30/20166/5/201601
AA4PA17/1/20167/5/201610
AA5PA27/1/20167/7/201610
AA6PA37/3/20167/7/201610
AA7

PA4

8/3/20168/6/201600
AA8PA58/4/20168/9/201600
AA9PA49/7/20169/10/201600

 

PA1, PA2, PA3 got readmitted within 30 days of their previous discharge, so their readmit_flag is 1 and the corresponding previous visit is flagged with PrevAdmit_flag as 1. PA4 was admitted after more than 30 days so flag is 0 for both readmit and prevadmit.

 

Please help me achieve this on the Load Editor. Thank you. 

1 Reply
Highlighted
Creator
Creator

If it is starting from 'now', I would create a temp table that has the max DateDischarged for each patient ID.

Then left join that max(DateDischarged) back to the main table and then do a DateDiff then use Interval(AdmissionDate - max(DateDischarged)) to see if the patient is flagged as discharged within the last 30days.

 

Thats my sketched out version for a process to use for future admissions. Though if you are looking through past data, this method won't find the correct Discharge Date to correspond to the 'last visit' if that patient has been discharged again.

 

My thought for that use case would be to load the records, group by patientId and order by discharge date, then potentially use a Previous() function to check each record against the record preceding it to compare the discharge date against the admission date?

 

Does any of that make sense?