Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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

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

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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;

View solution in original post

2 Replies
Highlighted
Partner
Partner

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;

View solution in original post

Highlighted
Contributor II
Contributor II

Amazing thank you so much!!!!!