EQuIS supports sample-specific action levels from the DT_ACTION_LEVEL_LOOKUP table.
Information about how to setup the Action Level to use the Lookup table and instructions on how to populate the necessary rows in the DT_ACTION_LEVEL_LOOKUP table for using Formulas are found in the help article Setup - DT_ACTION_LEVEL_LOOKUP. Refer to the articles listed below for information about using the other LOOKUP_METHODs.
•Using Lookups in DT_ACTION_LEVEL_LOOKUP
•Using Locations in DT_ACTION_LEVEL_LOOKUP
•MAG Sum and TEQ Sum in DT_ACTION_LEVEL_LOOKUP
Create Site-Specific Action Level Using FORMULA Methods
There are four FORMULA methods.
1.SINGLE_FORMULA: Uses certain calculations for determining the applied action levels. There are seven calculation options (detailed below).
2.SINGLE_MIXED: Uses the SINGLE_FORMULA along with SINGLE_LOOKUP method.
3.MULTIPLE_MIXED: Uses the SINGLE_FORMULA along with MULTIPLE_LOOKUP method.
4.GROUP_FORMULA: Uses certain calculations for determining the applied action levels. There are two calculation options (detailed below).
An example EDD with formula methods populated is shown below.
SINGLE_FORMULA
•LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source field. Example: USEPA
•LOOKUP_CODE (40 char): Used to create different categories for each source. Combining land use and matrix would be common entries for the LOOKUP_CODE. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries must match the method name exactly. In this case, that would be SINGLE_FORMULA.
•PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE field of the DT_ACTION_LEVEL_PARAMETER table.
Note: These first four fields are the link between the DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables.
•COMPARISON_GROUP (integer): This field is not used in the SINGLE_FORMULA method.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will be used in the calculation process.
•COMPARISON_OPERATOR (10 char): This field is populated with "CALC" or "NULL". The NULL value is used to create a default action level if the sample does not have a result value for the COMPARISON_ID.
•COMPARISON_VALUE (19 char): This field is not used in the SINGLE_FORMULA method.
•COMPARISON_UNIT (15 char): The unit of the lookupVALUE in the CALC_METHOD calculations is shown below. The lookupVALUE is the result value of the COMPARISON_ID analyte from the sample being processed. This value is converted to the COMPARISON_UNIT before the calculation is performed.
•ACTION_LEVEL (19 char): The default action level (COMPARISON_OPERATOR = NULL) value to use as the sample-specific action level for the PARAM_CODE analyte if/when the sample does not have a result value for the COMPARISON_ID. For the COMPARISON_OPERATOR = CALC row, this field is left blank since the sample-specific action level is calculated.
•ACTION_LEVEL_UNIT (15 char): The unit for the sample-specific action level.
•CALC_METHOD (255 char): The name of the action level calculation method. The value must match the appropriate CALC_METHOD name exactly. The seven CALC_METHOD options currently available are shown below. The lookupVALUE is the result value of the COMPARISON_ID analyte from the sample being processed. An explanation of the constants follows.
•CONSTANT_1 - 5 (19 char): These fields hold the constants used in the equations listed below.
For example, CONSTANT_1 = a, CONSTANT_2 = b, CONSTANT_3 = c, etc.
•REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).
CALC_METHOD |
Calculation |
|---|---|
CALC_MULTIPLY |
a + b * lookupVALUE |
CALC_QUAD |
a * lookupVALUE ^ 2 + b * lookupVALUE + c |
CALC_LOG |
a + b * Log(lookupVALUE) |
CALC_EXP_LOG |
a + b * Exp(c * Log(lookupVALUE) - d) |
CALC_LOG10 |
a + b * Log10(lookupVALUE) |
CALC_EXP10_LOG10 |
a + b * 10 ^ (c * Log10(lookupVALUE) - d) |
CALC_LOG_EXP_LOG |
(a - b * Log(lookupVALUE)) * Exp(c * Log(lookupVALUE) - d) |
CALC_EXP_QUAD |
a + b * Exp(c * lookupVALUE ^ 2 + d * lookupVALUE + e) |
Exp = e ^ ( )
Log = the natural logarithm
Log10 = base 10 logarithm
Notes: •Leaving a CONSTANT_* column blank does not eliminate it from the calculation. An error will occur if a blank exists for a constant included in the CALC_METHOD. Constants that are not included in the specified CALC_METHOD (such as CONSTANT_4, and CONSTANT_5 for CALC_METHOD = CALC_QUAD) can and should be left blank for clarity. •To eliminate an additive constant, enter the value = 0. •To eliminate a multiplicative constant without changing the equation, enter the value = 1. Entering a value = 0 into a multiplicative variable can be used to change the nature of an equation. As an example for the CALC_QUAD and CALC_EXP_QUAD equations, entering a zero in the b or d column, respectively, yields an equation that is not otherwise available. In this example, CALC_QUAD would become: a * lookupVALUE ^ 2 + c. |
During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the value of the COMPARISON_ID analyte from the same sample and use that value as the lookupVALUE in the above equations to calculate the sample-specific action level for the sample result.
Example
Action Level for Lead (CAS_RN 7439-92-1) = 1.2 * e ^ (0.34 * Ln(HARDNESS) + 0.056)
Populate the following columns of DT_ACTION_LEVEL_LOOKUP: LOOKUP_SOURCE: Agency's name (Abbr.) LOOKUP_CODE: User determined code LOOKUP_METHOD: 'SINGLE_FORMULA' PARAM_CODE: '7439-92-1' COMPARISON_ID: 'HARDNESS' COMPARISON_OPERATOR: 'CALC' COMPARISON_UNIT: 'mg/L' ACTION_LEVEL_UNIT: 'ug/L' CALC_METHOD: 'CALC_EXP_LOG' CONSTANT_1: 0 CONSTANT_2: 1.2 CONSTANT_3: 0.34 CONSTANT_4: -0.056 CONSTANT_5: NULL |
SINGLE_MIXED
•LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source field. Example: USEPA
•LOOKUP_CODE (40 char): Used to create different categories for each source. Combining land use and matrix would be common entries for the LOOKUP_CODE. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries must match the method name exactly. In this case, that would be SINGLE_MIXED.
•PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE field of the DT_ACTION_LEVEL_PARAMETER table.
Note: These first four fields are the link between the DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables.
•COMPARISON_GROUP (integer): This field is not used in the SINGLE_MIXED method.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will be used in the lookup and/or calculation process.
•COMPARISON_OPERATOR (10 char): This field and the other columns are populated as explained in the help article SINGLE_LOOKUP or is populated with "CALC", and the other columns are populated as explained above in the SINGLE_FORMULA section.
Note: The CALC row must be the last row entered for this option to process properly.
•REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).
During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the value of the COMPARISON_ID analyte from the same sample and then compare it to each COMPARISON_VALUE, one at a time from the top of the list, and use the first qualifying row's ACTION_LEVEL as the sample-specific action level for the sample result. When the processor reaches the CALC row, the comparison value is used to calculate the sample-specific action level for the sample result. The CALC row must be the last row entered for this option to process properly.
MULTIPLE_MIXED
•LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source field. Example: USEPA
•LOOKUP_CODE (40 char): Used to create different categories for each source. Combining land use and matrix would be common entries for the LOOKUP_CODE. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries must match the method name exactly. In this case, that would be MULTIPLE(AND)_MIXED or MULTIPLE(OR)_MIXED.
•PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE field of the DT_ACTION_LEVEL_PARAMETER table.
Note: These first four fields are the link between the DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables.
•COMPARISON_GROUP (integer): This field is used to create groups for the Multiple Lookup process. Each group is processed together, before moving on to the next group.
For MULTIPLE(AND)_MIXED, if all of the comparisons in the group are true, then the process ends and the sample-specific action level is assigned from that group.
For MULTIPLE(OR)_MIXED, if one of the comparisons in the group is true, then the process ends and the sample-specific action level is assigned from that group.
If multiple CALC rows exist in the same COMPARISON_GROUP, the action level will be used in alphabetical order. If this is not the desired order, assign the CALC rows new COMPARISON_GROUP numbers in the correct order. A COMPARISON_GROUP = 0 may be added to create a default action level value. Only the COMPARISON_ID = LOOKUP is processed for any zero group. The zero group is only processed if all of the other groups do not result in a positive result.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will be used in the lookup and/or calculation process.
•COMPARISON_OPERATOR (10 char): This column and the other columns are populated as explained in the help article MULTIPLE_LOOKUP or is populated with "CALC" and the other columns are populated as explained above in the SINGLE_FORMULA section.
Note: The CALC row must be the last row entered for this option to process properly. There is no NULL row for the MULTIPLE_MIXED methods; the zero group handles any null comparison values.
•REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).
During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the value of the COMPARISON_ID analyte from the same sample and then compare it to each COMPARISON_VALUE, one at a time from the top of the list, and use the first qualifying row's ACTION_LEVEL as the sample-specific action level for the sample result. When the processor reaches the CALC row, the comparison value is used to calculate the sample-specific action level for the sample result. The CALC row must be the last row entered for this option to process properly.
Note: The WARNING_LEVEL and WARNING_LEVEL_MIN fields in the DT_ACTION_LEVEL_LOOKUP table are not used for this Advanced Action Level. |
GROUP_FORMULA
•LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency's name or an abbreviation of the name is appropriate for the source field. Example: USEPA
•LOOKUP_CODE (40 char): Used to create different categories for each source. Combining land use and matrix would be common entries for the LOOKUP_CODE. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries must match one of the following method names exactly: GROUP_FORMULA, GROUP_FORMULA_2, or GROUP_FORMULA_3. (It is unusual for more than one value to work in this column, but for this particular Advanced Action Level, the user may need to run both CALC_METHODs below for the same PARAM_CODE to determine separate Acute and Chronic Action Levels.)
•PARAM_CODE (CAS_RN in the EDD) (20 char): This entry must equal the value in the PARAM_CODE field of the DT_ACTION_LEVEL_PARAMETER table.
Note: These first four fields are the link between the DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables.
•COMPARISON_GROUP (integer): Denotes which lookupVALUE (from the equation in the table below) the row represents (i.e., lookupVALUE_1 should have value 1, lookupVALUE_2 should have 2, etc.). This means that the rows can be added to the DT_ACTION_LEVEL_LOOKUP table in any order, if this column is populated with the correct integer.
•COMPARISON_ID (20 char): The CAS_RN of the analytes whose values will be used in the calculation process. The lookupVALUE is the result value of the COMPARISON_ID analyte from the sample being processed.
•COMPARISON_OPERATOR (10 char): This entry must be "GROUP_CALC".
•COMPARISON_VALUE (19 char): This field is not used in the GROUP FORMULA calculations and can be left blank.
•COMPARISON_UNIT (15 char): The unit of the lookupVALUE in the CALC_METHOD calculations is shown below. The lookupVALUE is the result value of the COMPARISON_ID analyte from the sample being processed. This value is converted to the COMPARISON_UNIT before the calculation is performed.
•ACTION_LEVEL (19 char): This field is left blank since the sample-specific action level is calculated.
•ACTION_LEVEL_UNIT (15 char): The unit for the sample-specific action level being calculated.
•CALC_METHOD (255 char): The name of the action level calculation method. The value must match the appropriate CALC_METHOD name exactly. The two CALC_METHOD options currently available are shown below.
•CONSTANT_1 - 5 (19 char): These fields hold the constants used in the equations listed below. See the Notes below the equations for an explanation of how each CONSTANT_* column in each row correlates to the constants in the two equations.
•REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE (only the first 255 characters will fit in ACTION_LEVEL_NOTE).
CALC_METHOD |
Calculation |
|---|---|
CALC_EXP_LOG_2 |
a + b * Exp(c * Log(lookupVALUE_1) + d * Log(lookupVALUE_2) + e) |
CALC_EXP_LOG_3 |
a + b * Exp(c * Log(lookupVALUE_1) + d * Log(lookupVALUE_2) + e * Log(lookupVAULE_3) + f) |
Exp = e ^ ( )
Log = the natural logarithm
Log10 = base 10 logarithm
Notes: •For the lookupVALUE_1 in CALC_EXP_LOG_2, that row’s CONSTANT_1 - 4 respectively are: a, b, c, e •For the lookupVALUE_2 in CALC_EXP_LOG_2, that row’s CONSTANT_1 = d •For the lookupVALUE_1 in CALC_EXP_LOG_3, that row’s CONSTANT 1 - 4 respectively are: a, b, c, f •For the lookupVALUE_2 in CALC_EXP_LOG_3, that row’s CONSTANT_1 = d •For the lookupVALUE_3 in CALC_EXP_LOG_3, that row’s CONSTANT_1 = e •Leaving a CONSTANT_* column blank does not eliminate it from the calculation. An error will occur if a blank exists for a constant included in the CALC_METHOD. Constants that are not included in the specified CALC_METHOD such as CONSTANT_2, and CONSTANT_3 for CALC_METHOD = CALC_EXP_LOG_2, can and should be left blank for clarity. •To eliminate an additive constant, enter the value = 0. •To eliminate a multiplicative constant without changing the equation, enter the value = 1. Entering a value = 0 into a multiplicative variable can be used to change the nature of an equation, but just using the proper equation is the recommended practice. |
During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the value of the COMPARISON_ID analyte from the same sample and use that value as the lookupVALUE in the above equations to calculate the sample-specific action level for the sample result.
Example
