topic Finding Difference in Date across rows in QlikView

Finding Difference in Date across rows

mellerbeck — Tue, 02 Dec 2014 22:24:58 GMT

So say there are 5 Patients and one of the Patients came in to the office today (12/02/2014) and they also came in previously less than 30 days ago. How would I display this one patient that matches the criteria in a chart?

LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [

PatientID, AppDate

1, 12/02/2014

1, 11/22/2014

2, 01/01/2014

3, 01/01/2014

4, 01/01/2014

5, 01/01/2014

];

Thanks for any thoughts!

-Michael

Re: Finding Difference in Date across rows

saurabh5 — Tue, 02 Dec 2014 22:59:27 GMT

hi Michael,

you can use a floor function to get the difference between the dates and use today() for latest date below is the script:

TEST:LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [
    PatientID, AppDate
    1, 12/02/2014
    1, 11/22/2014
    2, 01/01/2014
    3, 01/01/2014
    4, 01/01/2014
    5, 01/01/2014
];

NEW:LOAD PatientID,AppDate,FLOOR(TODAY()-AppDate) AS DIFF_daYSRESIDENT TEST;

and then use =count(distinct {<DIFF_daYS={'<30'}>}PatientID) for any chart to build as htis will give patient which visited less than 30 days.

Re: Finding Difference in Date across rows

mellerbeck — Tue, 02 Dec 2014 23:00:51 GMT

hmm maybe the max date minus the max date of rank 2

Re: Finding Difference in Date across rows

MK_QSL — Tue, 02 Dec 2014 23:39:27 GMT

Test:

LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [

PatientID, AppDate

1, 12/02/2014

1, 11/22/2014

2, 01/01/2014

3, 01/01/2014

4, 01/01/2014

5, 01/01/2014

];

Final:

Load

If(PatientID = Previous(PatientID) and Interval(AppDate - Previous(AppDate))<=30,'Yes','No') as ThirtyDaysVisitFlag

Resident Test

Order By PatientID, AppDate;

Drop Table Test;

Re: Finding Difference in Date across rows

MarcoWedel — Wed, 03 Dec 2014 00:25:55 GMT

Hi,

one solution to calculate the days since previous visit in the script could be:

// Test Data Generation
tabPatAppDat:
LOAD Ceil(Rand()*100) as PatientID,
    DayName(YearStart(Today())+Rand()*(DayNumberOfYear(Today()))) as AppDate
AutoGenerate 400;

// adding DaysSinceLastVisit field
Left Join (tabPatAppDat)
LOAD PatientID,
    AppDate,
    If(PatientID=Previous(PatientID),AppDate-Previous(AppDate)) as DaysSinceLastVisit
Resident tabPatAppDat
Order By PatientID, AppDate;

hope this helps

regards

Marco