Load data from table A based on field in table B

[ARe]

Hi

I'm somehow stuck. I got some patient data where a patient is identified by a patientfrid and for each patient there may be multiple cases (casefrid). one row per case.

Also there is a table with variables (gender, weight, ...) with some value and the information of the patient.

For each case I need to find the latest gender, weight, .... with respect to the enddate of the case (meaning last weight before case end).

[cases]:
load * inline
[casefrid, patientfrid, CaseStart, CaseEnd
cm1, pf1, 44930, 44950
];

[Variablen_tmp]:
load * inline
[patientfrid, valuetime, value, vvfrid, name
pf1, 44940, 80, vvfrid1, weight

pf1, 44990, 90, vvfrid1, weight
pf2, 44945, female, vvfrid2, gender
pf3, 44960, male, vvfrid3, gender
];

finding the latest weight is in principial quite easy using firstsortedvalue:

[casesNEW]:
load * resident cases;
left join load patientfrid,
FirstSortedValue(DISTINCT [value], -[valuetime]) AS 'Weight'
Resident [Variablen_tmp]
Where [name] = 'weight'
Group By [patientfrid];

but that gives me the latest weight for the patient (which is 90). I'm looking for the last weight before case end (which would be 80).

I would need so sort of "where valuetime <= caseend" but that is not possible (message: field not found).

I _think_ intervalmatch could help but I'm not sure how.

When I add

left join
intervalmatch(valuetime,patientfrid)
load distinct PeriopStart, PeriopEnd,patientfrid resident cases;

to the [Variablen_tmp] table then I'm still missing the casefrid which I need.

any suggestions?

Thanks!

BR Andreas

[KGalloway · Accepted solution]

Would something like the following work?

[cases]:
load * inline
[casefrid, patientfrid, CaseStart, CaseEnd
cm1, pf1, 44930, 44950
];

join

[Variablen_tmp]:
load * inline
[patientfrid, valuetime, value, vvfrid, name
pf1, 44940, 80, vvfrid1, weight

pf1, 44990, 90, vvfrid1, weight
pf2, 44945, female, vvfrid2, gender
pf3, 44960, male, vvfrid3, gender
];

[casesNEW]:
load
casefrid,
patientfrid,
FirstSortedValue(DISTINCT [value], -[valuetime]) AS "Weight"
Resident [cases]
Where [name] = 'weight'
and valuetime <= CaseEnd
Group By [casefrid], [patientfrid];

drop table [cases];

I get the following data from this:

[ARe]

thanks, quite easy with the join.

BR,

Andreas