1 Reply Latest reply on Feb 24, 2010 1:20 PM by philmodjunk

    Calculation - maximum value



      Calculation - maximum value


      Hi again,


      I have a few calculation questions for a medical database;


      There are 4 staging examinations a patient can have prior to having the surgery; each of these results is stored in separate fields CT_STAGE, CT_RESTAGE, MRI_STAGE & MRI Restage.  The lookup is a separate table and there is a relationship set between the main table and the lookup field.  Unfortunately, the values are not straightforward; 


      Here is how the table looks so far:


      Data Code   Code

      TX 1

      Tis  2

      T0 3

      T1 4

      T2 5

      T3 6



      I added the code so that they are in the correct order (nb these are not all the rows - there are 12).  The same lookup is used for each of the 4 fields with separate instances of the lookup table in the relationships graph.


      The  results may be:


      CT Stage   T3

      CT Resage T2

      MRI Stage T4

      MRI Resage T3


      So out of these 4 results I need T4 to be the result.  What is the best way to do this as a calculation?


      Also, within another table I have a similar problem this time just with a single field.  A patient may have multiple biopsies or minor resections of the tumours prior to having the final major surgery; I am using the same treatment table for all the treatments including the final surgery.  Ideally, I want to keep the fields the same for all these treatments.



      treatment 1   T1

      treatment 2  T2

      treatment 3 Tis

      treatment 4 T3

      treatment 5 T2

      major resection T4 


      So the result I need here is T3, but I need the maximum excluding the current record.


      The final one is one I asked previously but never managed to sort out;  i have time started and time finished set as time fields.


      so if a procedure starts at 23:50 and ends at 00:10 the result i need is 20.


      I thought if i could use


      (time start - time finish) + (60x24) (sixty minutes in an hour 24 hours per day)  Timestamp was suggested but the problem with that is that some of this is historical data and would be a nightmare to change to timestamps.   


      Any other suggestions?


      best wishes






        • 1. Re: Calculation - maximum value

          TX 1

          Tis  2

          T0 3

          T1 4

          T2 5

          T3 6



          First Case: This would be easier to handle if you had a table of related records like your second case of this instead of dedicated fields. If you also look up the codes for each field, you could set up a calculation field: Max ( CT Stage code ; CT Resage code ; MRI Stage code ; MRI Resage code ) and link it to a table Occurrence of your table to get the max Datacode.


          2nd Case: (table of related records ) define a relationship to the same source table with two pairs of fields, use ≠ with one pair to keep a record from matching to itself and use the second pair of records (using patientID?) to match to all the records in this group. Sort the relationship by the Code field (Again you'll need to look up this value as well as the Data Code) in descending order. Then you can refer directly to this related record to get the maximum data code excluding the current record.


          With your elapsed time calculations, you'll need to add at least 24 hours to Time Finish whenever Time finish < time start.


          Something like this: Time Finish + If ( Time Finish < Time Start ; 24 * 3600 ; 0 ) - Time Start


          That calc assumes no procedure exceeds 24 hours in duration. If that's a possibility, you'll need to put together a calculation that includes some date fields to effectively convert your times into Time Stamps.