Forward look, if patient has cancer found in procedure greater than current date

[mariusvr]

I need to find procedures where cancers could have been missed. I have two tables:

EXAM:

PATIENTID EXAMID EXAMDATE

patient1, exam1, 1-1-2010

patient2, exam2, 1-1-2015

patient1, exam3, 1-1-2011

patient2, exam4, 1-1-2019

HISTOLOGY

EXAMID, CANCER

exam1, 0

exam2, 0

exam3, 0

exam4, 1

Now I want an extra field in table EXAM, that looks up the CANCER value of the next procedure. For instance:

PATIENTID EXAMID EXAMDATE NEXT_CANCER

patient1, exam1, 1-1-2010,0

patient2, exam2, 1-1-2015, 1

patient1, exam3, 1-1-2011, NULL

patient2, exam4, 1-1-2019, NULL

I tried using:

=FirstSortedValue({<EXAMDATE ={">$=EXAMDATE"} >}CANCER,-[EXAMDATE])

But that does not work, once i add in the setexpression (to exclude prior dates). Ideally I would use it during the LOAD for performance, but it is hard as I need to match by PATIENTID and the histology table uses EXAMID.

Any tips

Thank you, Marius

[lironbaram · Accepted solution]

hi 

this script will get you the result , 
the script finds for each exam_id the future exam_id then using the future exam_id you get the result 

i assumed that exam_id is a unique value 

EXAM:
load * inline[
PATIENTID, EXAMID ,EXAMDATE
patient1, exam1, 1-1-2010
patient2, exam2, 1-1-2015
patient1, exam3, 1-1-2011
patient2, exam4, 1-1-2019
];
////////////////////////finiding the future exam for each exam 
EXAM2:
load *,
     if(Previous(PATIENTID)=PATIENTID,Previous(EXAMID) )   as futureExam
Resident EXAM
order	by PATIENTID, EXAMDATE desc
;

drop Table EXAM;




HISTOLOGY:
load * Inline [
EXAMID, CANCER
exam1, 0
exam2, 0
exam3, 0
exam4, 1
];

//////////////////////adding histology of the future exam
left join(EXAM2)
load EXAMID as futureExam,
     CANCER as futureCANCER
Resident HISTOLOGY;

[mariusvr]

Amazing thank you so much!!!!!