topic Re: Load data from table A based on field in table B in App Development

Load data from table A based on field in table B

ARe — Tue, 17 Jan 2023 13:28:44 GMT

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

Re: Load data from table A based on field in table B

KGalloway — Fri, 20 Jan 2023 21:33:08 GMT

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:

Re: Load data from table A based on field in table B

ARe — Mon, 23 Jan 2023 12:44:34 GMT

thanks, quite easy with the join.

BR,

Andreas