I’m pulling together a bunch of medical data for our patients who have surgery at our hospital.
The data is being pulled from a bunch of different machines, spreadsheets, workbooks, etc and these measurements happen on different days during the rehabilitation period.
I’m trying to figure out the best way to parse and model the data.
I have a single table identifying the surgery date for each individual, along with other demographic information (fDemographics in the image below).
Most of my analyses will have “Days since surgery” on the x-axis - i.e. the difference between the particular assessment date for that individual, and the date of that individual’s surgery.
As I see it I could do a LOOKUPVALUE for every individual assessment type table for the surgery date, and then for every assessment have a “Days since surgery” column.
This approach works, but strikes me as inelegant, and prone to error as the end users will have to remember to choose the ‘correct’ ‘Days since surgery’ value for the x-axis for every visualisation.
The other thought I had was an equally inelegant merging of all the different assessment tables so there’s simply the one data table for every assessment value, and then one patient dimension table (each patient is uniquely identified by a Medical Record Number)
I’ve tried to visualise the problem here:
I’d appreciate any thoughts, especially if I’ve missed something very obvious here (which is likely)