EQuIS supports sample-specific action levels from the DT_ACTION_LEVEL_LOOKUP table.
Details about how to set up the Action Level to use the Lookup table, and how to populate the necessary rows in DT_ACTION_LEVEL_LOOKUP for using LOOKUPS, is available in the help article, Setup - DT_ACTION_LEVEL_LOOKUP. Additional information on the other LOOKUP_METHODs is found in the following Advanced Action Levels help articles.
•Using Formulas in DT_ACTION_LEVEL_LOOKUP
•Using Locations in DT_ACTION_LEVEL_LOOKUP
•MAG Sum and TEQ Sum in DT_ACTION_LEVEL_LOOKUP
Creation of Sample-Specific Action Level Using LOOKUP Methods
There are three LOOKUP Methods, each explained below.
1.SINGLE_LOOKUP: The Action Level Exceedance II Report applies the specified action level based on results for a single comparison analyte (specified by COMPARISON_ID).
For example, if Hardness is < 100 mg/l in that sample, the action level for Lead = 1.1.
2.MULTIPLE(OR)_LOOKUP: The Action Level Exceedance II Report applies specified action level if any of the comparisons within a COMPARISON_GROUP are met.
For example,
a.IF 100 mg/L < Hardness < 200 mg/l
or
b.IF 5.5 pH units < pH < 6.8 pH units.
Then the action level for Lead = 2.2.
3.MULTIPLE(AND)_LOOKUP: The Action Level Exceedance II Report applies specified action level if all of the comparisons within a COMPARISON_GROUP are met.
For example,
a.IF 100 mg/L < Hardness < 200 mg/l
and
b.IF 5.5 pH units < pH < 6.8 pH units.
Then the action level for Dissolved Oxygen = 2.2.
Example EDD with All Three Methods Populated
SINGLE_LOOKUP
•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_LOOKUP.
•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.
The first four columns above are the link between DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP.
•COMPARISON_GROUP (integer): This column is used in SINGLE_LOOKUP to keep the order correct even though each “group” is only one row each. The order is very important. For example, if the first row is HARDNESS < 200 and the second row is HARDNESS < 100, then a sample with HARDNESS = 88 will trigger the first row instead of the second row because 88 is also less than 200.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process.
•COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are <, <=, >, >= and the special value of NULL (for when there is no result for the COMPARISON_ID).
•COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID's analytical result value.
•COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE
•ACTION_LEVEL (19 char): The value to use as the sample-specific Advanced Action Level for the PARAM_CODE analyte.
oTo skip samples with values of the COMPARISON_ID analyte in a given range (e.g., HARDNESS < 100), enter the text value “NULL” in the ACTION_LEVEL column.
oWhen the “NULL” option is used the “al_remark” column will be populated with this message: "Advanced Action Level Lookup has been set to NULL".
•ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level.
•ACTION_LEVEL_MIN (19 char): The value to use as the sample-specific Advanced Action Level Minimum for the PARAM_CODE analyte.
•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 Reports, 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 compare it to each COMPARISON_VALUE, one at a time from the top of the list; and will use the first qualifying row's ACTION_LEVEL (and/or ACTION_LEVEL_MIN if populated) as the sample-specific Advanced Action Level for the sample result.
MULTIPLE(OR)_LOOKUP
•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(OR)_LOOKUP.
•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.
The first four columns above 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. If one of the comparisons in the group are true, then the process ends and the sample-specific action level is assigned from that group.
oA 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, for example, when there is no result in the sample for any of the COMPARISON_ID analytes.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process. For the Multiple Lookup process, each COMPARISON_GROUP needs to include two or more CAS_RNs and one COMPARISON_ID = LOOKUP.
•COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are NULL, <, <=, >, and >=. For the COMPARISON_ID = LOOKUP row, the value does not matter, but, "=" is suggested for clarity.
•COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID'S analytical result value. For the COMPARISON_ID = LOOKUP row, the value does not matter, but leaving this blank is suggested for clarity.
•COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE
•ACTION_LEVEL (19 char): The value to use as the sample-specific Advanced Action Level for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
oTo skip samples with values of the COMPARISON_ID analyte in a given range (e.g., HARDNESS < 100), enter the text value “NULL” in the ACTION_LEVEL column.
oWhen the “NULL” option is used the “al_remark” column will be populated with this message: "Advanced Action Level Lookup has been set to NULL".
•ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
•ACTION_LEVEL_MIN (19 char): The value to use as the sample-specific Advanced Action Level Minimum for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
•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 values of the COMPARISON_ID analytes from the same sample, compare them each to COMPARISON_VALUE, one group at a time, in numerical order, and will use the first qualifying group's LOOKUP row's ACTION_LEVEL (and/or ACTION_LEVEL_MIN if populated) as the sample-specific Advanced Action Level for the sample result. A group qualifies when one of the comparisons in the group are true.
MULTIPLE(AND)_LOOKUP
•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)_LOOKUP.
•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.
The first four columns above 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. If ALL of the comparisons in the group is true, then the process ends and the sample-specific Advanced Action Level is assigned from that group's LOOKUP row.
oTo enable a default action level in the report when conditions for all other groups do not apply, create a record with COMPARISON_GROUP = 0 and COMPARISON_ID = LOOKUP. 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, for example when there is no result in the sample for any of the COMPARISON_ID analytes.
•COMPARISON_ID (20 char): The CAS_RN of the analyte whose value will control the lookup process. For the Multiple Lookup process, each COMPARISON_GROUP needs to include two or more CAS_Rns and one COMPARISON_ID = LOOKUP.
•COMPARISON_OPERATOR (10 char): The operator to use in the comparison between the analytical result value and the COMPARISON_VALUE. Accepted values are NULL, <, <=, >, and >=. For the COMPARISON_ID = LOOKUP row, the value does not matter, but "=" is suggested for clarity.
•COMPARISON_VALUE (19 char): The value to compare to the COMPARISON_ID'S analytical result value. For the COMPARISON_ID = LOOKUP row, the value does not matter, but leaving this blank is suggested for clarity.
•COMPARISON_UNIT (15 char): The units of the COMPARISON_VALUE
•ACTION_LEVEL (19 char): The value to use as the sample-specific action level for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
oTo skip samples with values of the COMPARISON_ID analyte in a given range (e.g., HARDNESS < 100), enter the text value “NULL” in the ACTION_LEVEL column.
oWhen the “NULL” option is used, the “al_remark” column will be populated with this message: "Advanced Action Level Lookup has been set to NULL".
•ACTION_LEVEL_UNIT (15 char): The units for the sample-specific action level. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
•ACTION_LEVEL_MIN (19 char): The value to use as the sample-specific Advanced Action Level Minimum for the PARAM_CODE analyte. Only the value in the COMPARISON_ID = LOOKUP row is used for this Multiple Lookup process.
•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 Report, for each PARAM_CODE sample result, the Advance Action Level processor will find the values of the COMPARISON_ID analytes from the same sample, compare them to each COMPARISON_VALUE, one group at a time in numerical order; and will use the first qualifying group's LOOKUP row's ACTION_LEVEL (and/or ACTION_LEVEL_MIN if populated) as the sample-specific Advanced Action Level for the sample result. A group qualifies when ALL of the comparisons in the group are true.
Troubleshooting
If your Action Level Exceedance II report output includes records with "Advanced Action Level Missing or Not Numeric" in the FLAG column, there was an issue with the setup of your action level. This error can often be resolved by adding a COMPARISON_GROUP = 0 record to DT_ACTION_LEVEL_LOOKUP to account for samples with COMPARISON_ID analyte results missing.
Note: The WARNING_LEVEL and WARNING_LEVEL_MIN columns in DT_ACTION_LEVEL_LOOKUP are not used for this Advanced Action Level. |