Attached is one method that uses a self join on the data.
Step 1 - I've added some Custom SQL to remove the duplicates records for a given patient (as identified by the Medical #). The resulting new data source only includes one records per patient and only contains the Medical #, Admit Date and Discharge Date.
Step 2 - Create a calculation using the DATEDIFF function on the new data source.
Step 3 - Add the Medical # to the Rows.
Step 4 - Drag the calculation from the new Data Source onto the existing table ... the Medical # should be recognized as the column to join on.
There may be other ways (window function with lookup??), but this works. Just wait until you get non-contiguous overlapping admit/discharge records... then the fun begins.
** The attachment appears to be corrupted ... do not use. **
Test_Data_sharing_JJS.twbx.zip 36.0 KB
1 of 1 people found this helpful
Not sure why, but after saving it as a packaged workbook and reopening, the excel files were no longer valid.
I've rebuilt it from scratch in the attached file.
The "Admission Count" is far easier to calculate (you could just do a COUNTD([Medical #]). I used the data join from the other table as an example.
The data blending videos explain it better than I could. Check them out!