Using Formulas in DT_ACTION_LEVEL_LOOKUP

<< Click to Display Table of Contents >>

Navigation:  Professional > Action Levels > Advanced Action Levels > User Configured Advanced Action Levels >

Using Formulas in DT_ACTION_LEVEL_LOOKUP

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.

 

clip0106_zoom75

 

 

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).

 

15283-single_formula_zoom65

 

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).

 

15283-single_mixed_zoom65

 

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).

 

15283-dt_action_level_lookup_mixed_zoom70

 

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

 

pro_aal-group-formula-example_zoom55