4 Replies Latest reply: Oct 13, 2016 2:38 AM by Ravi Paw RSS

    Calculating a formula field from other rows.

    Ravi Paw

      Hi All,

      I am difficulty calculating some fields within the Model I have created below. I hope someone can aid me.


      I have the following data captured below, what I need to do is in the “CONCENTRATION” field I need to calculate a number from the other Cells.


      For Example let’s take the ASSAYNAME - “FLC Ratio”. This “FLC Ratio” is calculated from dividing the KFree Value (138.54)  by the L Free Value (105.80). The resultant of this is 1.30945.


      What I need to do is Input a Formula Field within CONCENTRATION to calculate the The FLC Ratio value, is this possible? I do know how to calculate by Columns, which is relatively straightforward, but by Cell, I am struggling to get the concept.


      I hope this makes sense.


      Many thanks in advance for your help.







        • Re: Calculating a formula field from other rows.
          Chris Porthouse

          My first thought would be to export this data into a spreadsheet and use a second model to use this data as an external lookup.  You can still use the original report file, but now you can create a external lookup to lookup the cells you need. With automator, you can easily automate this process.

          • Re: Calculating a formula field from other rows.
            MelaSarenas _

            Hi Ravi,


            Another way would be to use Data Prep Studio of Monarch Complete.


            Assuming that your sample data is one group, we can open it in Data Prep Studio and pivot on AssayName and ConcentrationRaw as the value.  Then group by LotNumber.  Once this is set we can then combine with the original data set and you can now create a calculated field using KFree and LFree.


            Hope this helps.


            • Re: Calculating a formula field from other rows.
              Grant Perkins

              Hi Ravi,


              Am I correct to assume that the rows are really multiple data lines (and therefore fields in effect) within the same record - Lot Number?


              If so one might trap them as such  - but as I recall your "reports" can present with some specific challenges that might make that tricky.


              In Monarch Classic the parallel approach to Mela's Data Prep Studio approach (assuming that all records are NOT in the same "record" once they have been extracted) would be to "Pivot" using a Summary. That could get your calculation but you would then need to Export the result and run a lookup to a new calculated field ("Concentration Processed"?) to bring the result into the table.


              If, however, all of the value fields are in the same row as a single Detail record you can calculate away with them as much as you like!


              If the data in your table is starting out that as you show it and not in a "report" style then the Pivoting, by whichever means you prefer, and a subsequent lookup is as good an approach as any to a "2 stage" process.


              An alternative would be to move the columns in the table around a bit and create a "report" file as text. Then output to a text file which you can read in as a "Report" and so organise things to get all of the fields into a single record by Lot Number. Once in that for the model is very simple and you can calculate directly in the table just as you are currently trying to do.


              That may sound rather complicated and roundabout as a method but in fact it should be very easy and result in simple and easy to maintain process.


              Chris's suggestion follows the same principle. Whether you choose to output to an Excel file (actually if it is just for lookup purposes you might find an Access file type better in order to avoid the sometimes unusual results one can get with Excel versions) or a flat text file, probably doesn't make a lot of difference. Go with what you feel is the most appropriate approach.