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

# Calculating a formula field from other rows.

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.

Ravi

• ###### Re: Calculating a formula field from other rows.

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.

Thanks Chris, this seems like a good idea. Would you aid me in how to do this please? At the moment the model I created above works with automation process. So a secondary Model can be created to link this data with an external Lookup?

How would I go about doing this in automator?

Many thanks for your help

Ravi

• ###### Re: Calculating a formula field from other rows.

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.

Mela

• ###### Re: Calculating a formula field from other rows.

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.

HTH.

Grant