Formulas in Measurement Values
This page covers how GeoDin organizes measurement values, how the Calculation function recalculates a measurement table using predefined or single formulas, the full formula and condition syntax with its special-case constructions, and the import/export, diagram, and table-format options of the measurement editor.
Measurement values
GeoDin organizes objects spatially. These are point objects with or without a depth value. At these objects measurements can be made. In order to use GeoDin to collect such data, measurement points need to be defined in the general data. Usually filters and sample intervals are used as measurement points. Also the object itself can be defined as a measurement point. In the GeoDin object manager measurement points are shown by three blue spheres.
GeoDin Demo Project
Object
All objects
General borehole log
Measurement point
filters
upper piezometer: (4.3-6.3m)
lower piezometer (7.8-8.7m)
samples
BH01: (1-7m)
BH01: (4-5m)
Terminology
The following hierarchy is used in the measurement point organization to relate a single measured value to a measurement object of the measurement point. There are the following different types:
Measurement point type
The measurement point type defines, what type of object it is. These can be either with or without a vertical component. An example of a measurement point with a vertical component is a borehole. A borehole can be the measurement point itself (e.g. where the whole length is sampled) or other measurement point types can be associated with it (discrete samples at various intervals over the length of the borehole). Examples of point samples (i.e. without a vertical component) are surface water or climate measuring stations.
Data type
Chemical investigations can be done for several objects for each type of measurement point. For these combinations data types are defined. For example at a groundwater well the water quality can be investigated or flow rates measured. For each case there is a data type. Each data type can be assigned to several measurement point types. So the data type "groundwater composition" can be entered for a groundwater measurement point as well as for a well. The results are combined in a data type table, although the data for each measurement point are distinct from one another.
Chemical group
Because the number of individual parameters within a data type can reach large amounts, the parameters are subdivided into chemical groups to allow a better overview. Each group is distinguished by a similarity in the chemical parameters or descriptive characteristics and may have up to 20 parameters.
Parameter
A parameter is an individual measurement described by a name, a field identification and a unit
Query
Queries are used within projects or databases to interrogate data. They define the amount and type of data from which the results are derived.
Special values
GeoDin organizes measurement values as numerical entries. Hence values below a detection limit cannot be saved as the character „<". In such cases a negative detection limit is entered (e.g. "-1"). These values are ignored by statistical analyses. If the detection limit is unknown (e.g. old data) the value"-88" is used. If the value is not detectable then"-99" should be entered:
-XX
beneath detection limit (XX = detection limit)
-88
beneath detection limit (detection limit value unknown)
-99
not detectable
Formula
As an alternative to presenting the measurement values in grid form you may view the current data set in a mask. At the top of the mask the general sample data (Name, Date, Time) and the group are displayed. Below the individual parameters for the current data set are listed in rows. For each parameter the name, measurement value, unit, detection limit and investigation method are shown. Name and unit are not editable. The contents of a data set can be saved as a simple text file (which can be subsequently loaded). By pressing the OK button the mask contents are saved to the data set - by pressing Cancel the contents are discarded. Optionally the short field name can be used for the parameter column.
Calculation
This function allows you to recalculate values for entire table in the measurement editor. You have two options:
-Available formulae-
Execute one or more formulae from the predefined Formulasof the data type, found in the system configuration.
If you check the box [only activate formulae] you will only see formulae which are set to active in the General formulas in the system configuration. Note: These formulae will always be executed for the selected data records when using the Calculate function. This is because an update of a data record triggers the calculation of active formulas.
Formulae are marked with a red symbol instead of a black one if the target field of the formulas is always meant to be overwritten.
Formulae can be sorted by clicking on the actual column title, e. g. name or target field. Note: the execution order of the selected formulae will be the same as it has been set in the data type settings of the system configuration! **This is important, especially for interdependent formulae.
-Execute single formulas-
To execute a single formula you may use and change one of the predefined formulas from the General formulas of the system configuration or you define a new formula.
To accept a formula just mark it by clicking on the name (you do not have to check the box for this action) and click the button Accept available marked forumla. The fields "Target field:", "Condition:" and "Formula:" are automatically filled and can be edited.
Alternatively you can edit these fields without using a predefined formula but creating a new formula, which is applied to the target field.
[[Overwrite target]]{.underline}
For the following calculation you can set the configuration to overwrite existing values. By default the calculation won't overwrite existing fields but calculate results for those fields without values for the corresponding target field.
[[All parameters have values]]{.underline}
Furthermore you can specify only to execute the calculation if all parameters, which are used in the formula, contain values for the calculation. Therefore the calculation won't be executed for empty data fields (if they are used in the formula).
-All visible data records-
Choose here whether the calculation is done for all data records listed in the measurement data editor.
-All selected data records-
Choose this option to execute the calculation only for the data records (rows) selected in the measurement data editor.
Reference: Definition of formulas
A formula is defined as a string of characters (similar to a text macro definition) and contains mathematical operators for calculating a result.
For example: $DAT.PAR1$ * 100
The characters inside the $-signs relate to a GeoDin data field. The following operators can be used:
(x) stands for the table column of GeoDin (e.g. $DAT:PAR1$)
Empty spaces can be contained in the formulas. Fixed number values (100 in the example above), can be entered directly in the formula.
Use of conditions
Note:
The formula can be entered directly in the measurement editor after clicking the button or on the system tab under Data types-> Data type settings->Formulas (General formulas).
Example for simple conditionExample:
Destination: WAS:NA_CALC
Condition: $WAS:MG$>3
Formula: $WAS:NA$/2
The target parameter NA_CALC is calculated if the parameter MG has a value of 3 or higher.
Example for multiple conditionExample:
Destination: WAS:NA_CALC
Condition: $WAS:MG$>3 AND $WAS:CA$<10
Formula: $WAS:NA$/2
The target parameter is calculated, if both the parameter MG has a value greater than three and the parameter CA has a value of less than 10.
Conditions for changing values
By using the formatting @O the original value of a data record BEFORE the last change can be recreated in the measurement editor. Hence checking for differences is possible.
Example:
Condition: $WAS:PAR1$ - $WAS:PAR1@O$ >10
The condition is true, when the value of PAR1 in the cell is more than ten times the previously entered value.
Further condition examples
In a condition, the NULL operator can be used. It defines whether a parameter has a value.
Example:
Destination: WAS:NA_CALC
Condition: $WAS:MG$>3 AND $WAS:CA$=NULL
Formula: $WAS:NA$/2
The target parameter NA_CALC is calculated by taking half the value of the parameter NA, if the value of the parameter MG exceeds 3 and the parameter CA is empty.
If strings are used in a condition, the text has to be included in inverted (or high) commas. Missing inverted commas and mis-spelling are interpreted as non-equal. The spelling of the condition is case sensitive.
Example:
Destination: WAS:NA_CALC
Condition: $BEARBEIT$='Müller'
Formula: $WAS:NA$/2
The target parameter NA_CALC is calculated as half of the parameter NA, if the author of the data record has the name Müller.
Using special rules
Additionally to the mathematical operators special syntax constructions can be used for the usage of values from the GeoDin tables to take into consideration numerous special cases.
Special cases in formula syntaxDetection Limits
Detection limits present a special case. These are by definition negative values (e.g. -1 for <1). If these values are used without care, false results may be produced, for example when building sums from individual parameters. To do this, a construction in the form of @B(x) within the $-signs must be used, where x is a factor with which the detection limit enters the calculation. For example a detection limit of 5 mg (entered as -5) using the factor 0.5 produces the result 2.5.
Example: $WAS:BENZEN@B(0,5)$+$WAS:TOLUEN@B(0,5)$+$WAS:XYLEN@B(0,5)$
In the case above, where values for individual parameters of -5 or -1 are found, the sum calculation uses half of these values.
Default values
For certain calculations it may be necessary to work with predefined settings or defaults. When a parameter is either not present or has not been analyzed in a data set, a standard value can be assumed and used for calculation. This is realized by using the construct @D(x) inside the $ signs, whereby x is the predefined default value used when no value is present in the field.
Example: VALUE=$ORGANIC@D(10)$/$CLAY@D(25)
The calculated value has the quotients from the organic substances and clay in a soil sample. If no values are present in these fields the default values are used.
Mean Value
A mean value is calculated by using the symbols "@M" inside the dollar symbols of a formula. The individual values are separated by ";" and only filled fields can be used.
Example: UWDRYMIN=$UWDRYMIN1;UWDRYMIN2;UWDRYMIN3@M$
The result is an average of UWDRYMIN1 to UWDRYMIN3.
Using a number from a dictionary
If a dictionary is used, which produces a number when entering a code, this can be used for a calculation. By using the "@R" sign a recode is carried out.
Example: CU=($CONE@R$)/SQRT($PEN1;PEN2;PEN3;PEN4;PEN5@M$)
First of all the entry for CONE is replaced by the value from the dictionary. For the values P1 to P5 an average is taken from which the square root is calculated. The value for CONE is then divided by this value.
Using values from another data type
Values from one data type can be used in calculating values in other data types. To do this, the code of a data type is followed by a colon. The relationship to a data record in another data type is defined by time. To compare values the date is used in a number of different ways:
[=SMPDATE] or no definition
The date must be the same
[<=SMPDATE]
The date can be the same or less than
[<SMPDATE]
The date must be less than
[>=SMPDATE]
The date can be the same or more than
[<SMPDATE]
The date must be more than
Example: WASSPNN=$ROK:ROKNN[<=SMPDATE]$-$WASSPROK$
The water level expressed in meters above sea level is calculated by using a value from the data type ROK (top of piezometer). The value used can be from the same day or the next most recent value. From this value the current level is subtracted.
Using values from measurement point general data
It is possible to incorporate general data fields in formula for measurement points. The relationship is defined as follows: $Tablel.Datafield$.
Example: $ASBFILTR.INVMBEG$-$WST:WASSPROK$
The water level in the destination WASSPNN calculated using the measurement point elevation ($ASBFILTR.INVMBEG$) and the measured water level from the top of the pipe $WST:WASSPROK$ in the data type.
Ionic Balance
By using the symbol %IONB the ionic balance can be calculated and used as result.
Example: IONICBALA=$%IONB$
Attention: For a correct calculation the fields with the names, which are expected by the calculation, must exist and be in use (see Ion balance).
Automatic numbering
To automatically assign consecutive numbers to a parameter, the expression $%FIRSTID:PARAMETER$ can be used. The numbering for the corresponding parameter always starts at 1.
Example: $%FIRSTID:TESTNO$
The test number is automatically assigned a consecutive number in the TESTNO field for each record. The first record is given the number 1.
Further Symbols
$%PI$ produces the number Pi
$%USERNAME$ can use a (text-)formula, to create the name of the current database user
$%NOW$ results the current date and time
Object reference
$%OBJECTID$ Access to the LOCID for general data tables if available
$%PRJID$ Access to the PRJ_ID for general data tables if available
Text exchange - Formulas to create formatted text
By activating the control box [Text exchange (no calculation)] a calculation is prevented when carrying out the formula. This option is only useful, where a string parameter as result is required. The result is that the parameters are replaced by a string of actual values, whereby no calculation is carried out.
Example: $LOCREG.SHORTNAME$ / $SMPDATE$
In the selected target field a combination of the object short description, an oblique and the date is created, for example "Brg 12 / 12.10.2004".
Text exchange with Macro
In addition to the text exchange, format specifications can be resolved.
Example: $LOCREG.SHORTNAME$ from $SMPDATE@dd.mmmm.yyyy$
Attention: Only parameters of the same table (data type) or object type parameters can be evaluated. The parameter of the current table must be specified here without the table abbreviation. See example.
Reference: Import/Export
In the GeoDin object manager at the level of a measurement point or a group of measurements the methods "Export measurement values" and 
"Import measurement values" can be selected.
By starting this method a dialogue appears where all import or export settings can be made:
Diagrams and analysis
If the checkbox [diagrams and analysis] is activated, additional information for the current data pool is shown below the data entry grid.
Column chart
The values of the current column are graphically represented in the order that the data sets are displayed in the data entry grid.
To represent a particular parameter in the chart (to fill the chart) one data set of the appropriate column has to be selected (e.g. a data record of the column CHLORIDE). The current data record will be displayed as a filled rectangle in the chart. With a click on the rectangle (filled or not filled) it is possible to navigate to the data record in the data entry grid.
Row chart
Graphical representation of the values of the current row (data record) in the order that the columns are displayed in the data entry grid. To navigate to the column in the data entry grid click on the bar of the desired parameter in the row chart.
Plausibility control
This tab displays the plausibilities analysis of the currently checked row. A Plausibility can be defined within the Properties.
Formulae
The last executed formulae will be displayed when a new data set is recorded. The General formulas can be defined directly within the method "Measurement data" or within the Properties.
List comparison
The chosen list comparison of the current data record will be carried out and the result displayed. The List group can be created und managed within the Properties.
Ionic balance
The ionic balance will be displayed for the current data record. The Ion balance is calculated and evaluated based on the DVWK 1992 recommendations.
Format options
GeoDin supports two general arrangements of tabular data to be imported.
The format -Table by row- describes a table, which contains each sample in one row, the values of the parameters are stored in separate columns for each parameter.
The format -Table by column- describes a table, in which one measurement of one parameter builds one row. A sample can consist of a certain number of rows (as many as measured parameters) in this format. This format also allows additional information for each measured parameter to be imported and organised in GeoDin too.
To import data from this type of table you have to first make further adjustments. At first choose the columns, which group the data records of one sample. Therefore tick the appropriate columns in the list of "Grouping data fields"; in the example above tick the columns SAMPLE and DATE. The result of this choice would be that the first two rows (Sample 1 from 12.07.2012) would generate one cumulative import row and the third row (Sample 2 from 12.08.2012) another.
Choose from the drop-down list "Data field with parameter name:" the column, which contains the parameter name or id; PARAM in the example above.
From the drop-down list "Data field with measurement value:" please choose the column, which contains the value of the parameter; in the example above VALUE.
GeoDin now will transform the import table to the table format -table by rows- (please see above). The preview of the import data of our example now will be displayed as follows:
Please note that the information from the column COMMENT isn't lost. The import preview the cells of the measurement values are tagged at the right top corner with a red triangle. To display the additional information for the measurement value, please hold the mouse pointer over this corner. For the cell NA=2,4 the information 'COMMENT:verified' would be shown. This information can be imported using the Additional measurement information.
There is also a special pre-formatting for text files in the Octoware format available. There are no further adjustments necessary for this format.
Example fragment of an Octoware file:
Related topics
Shared measurement/data-type reference content now lives in the measurement-data editor reference (Working with Measurement Data)
Last updated
Was this helpful?