I am trying to perform a funky calculation and need some help!
I want to work out the dose a patient recieved based upon the concentration (CONC), amount (DOSEVOL) and bodyweight (BW). Its an easy enough expression (CONC * DOSEVOL)/BW, this gives me the dose given in mg/kg - perfect.
....BUT in the datamodel the CONC and DOSEVOLS are in a different table (dosingTable) to the BW (bodyweightTable). And each event (either dosing or measuring BW) are given different timestamps (AbsDOSING_TIME and AbsBW_TIME). Now in most caseswhat happens is that there is a week of dosing between BW measurements.
So in order to perform the calculation I want to create a new column in dosingTable that places the BW measured most recently (relative to the AbsDOSING_TIME) for each dosing event.
What expression do I need to use in the load script to find the most recent (relative to each dosing event) bodyweight to drop in to the table?
What I'd try is concatenating all the events (dosing, measuring, ...) into the same table, with 2 Timestamp fields - one for dosing and one for Measuring. In addition, I'd also have a combined Timestamp that could be populated from eather one of them.
Then, you could reload the combined table, sorting by Patient and Timestamp. Based on the sequence of events, you could populate the Measuring Timestamp (using PREVIOUS()) for all the Dosing events and vice versa, if needed...
