Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mellerbeck
Creator II
Creator II

Finding Difference in Date across rows

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

4 Replies
saurabh5
Creator II
Creator II

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.

mellerbeck
Creator II
Creator II
Author

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

MK_QSL
MVP
MVP

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;

View solution in original post

MarcoWedel

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