Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Amazing thank you so much!!!!!