EQuIS supports sample-specific action levels from the DT_ACTION_LEVEL_LOOKUP table.
Information about how to set up the Action Level to use the Lookup table, and instructions on how to populate the necessary rows in DT_ACTION_LEVEL_LOOKUP for using Formulas is 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
Creation of Site-Specific Action Level using FORMULA Methods
There are three 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.
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 column. Example: USEPA
•LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries in this column 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 column of DT_ACTION_LEVEL_PARAMETER.
These first four columns are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.
•COMPARISON_GROUP (integer): This column is not used in the SINGLE_FORMULA
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will be used in the calculation process.
•COMPARISON_OPERATOR (10 char): This column 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 column is not used in the SINGLE_FORMULA.
•COMPARISON_UNIT (15 char): The unit of the compValue in the CALC_METHOD calculations is shown below. The compValue 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 compValue 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 columns 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 * compValue |
|||
CALC_QUAD |
a * compValue ^ 2 + b * compValue + c |
|||
CALC_LOG |
a + b * Log(compValue) |
|||
CALC_EXP_LOG |
a + b * Exp(c * Log(compValue) - d) |
|||
CALC_LOG10 |
a + b * Log10(compValue) |
|||
CALC_EXP10_LOG10 |
a + b * 10 ^ (c * Log10(compValue) - d) |
|||
CALC_LOG_EXP_LOG |
(a - b * Log(compValue)) * Exp(c * Log(compValue) - d) |
|||
CALC_EXP_QUAD |
a + b * Exp(c * compValue ^ 2 + d * compValue + e) |
|||
|
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, 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 compValue 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 column. Example: USEPA
•LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries in this column 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 column of DT_ACTION_LEVEL_PARAMETER.
These first four columns are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.
•COMPARISON_GROUP (integer): This column is not used in the SINGLE_MIXED.
•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 SINGLE_LOOKUP or it is populated with CALC, and the other columns are populated as explained above in the SINGLE_FORMULA section.
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; 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 column. Example: USEPA
•LOOKUP_CODE (40 char): This column is used to create different categories for each source. Combining land use and matrix would be common entries for the code column. Example: Comm_Surf_Soil
•LOOKUP_METHOD (255 char): The entries in this column 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 column of DT_ACTION_LEVEL_PARAMETER.
These first four columns are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.
•COMPARISON_GROUP (integer): This column 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 your 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 it is populated with CALC and the other columns are populated as explained above in the SINGLE_FORMULA section. The CALC row must be the last row entered for this option to process properly. Note that there is no NULL row for the MULTIPLE_MIXED; 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, 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 columns in DT_ACTION_LEVEL_LOOKUP are not used for this Advanced Action Level. |