Using Locations in DT_ACTION_LEVEL_LOOKUP

<< Click to Display Table of Contents >>

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

Using Locations in DT_ACTION_LEVEL_LOOKUP

EQuIS supports location-specific action levels from the DT_ACTION_LEVEL_LOOKUP table.

 

For information 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, read this help article. For using the other LOOKUP_METHODs, refer to the articles below.

 

Using Formulas in DT_ACTION_LEVEL_LOOKUP

Using Lookups in DT_ACTION_LEVEL_LOOKUP

MAG Sum and TEQ Sum in DT_ACTION_LEVEL_LOOKUP

 

Creation of Location-Specific Action Level Using LOCATION Method

 

To create a Location-specific Action Level, in the DT_ACTION_LEVEL_LOOKUP table add the following:

PARAM_CODE = CAS_RN for each analyte to be processed.

LOOKUP_METHOD = ‘LOCATION’

COMPARISON_OPERATOR / COMPARISON_ID combinations as follows:

oCOMPARISON_OPERATOR = ‘SINGLE’ / COMPARISON_ID = SYS_LOC_CODE for an individual location with its own unique Action Level.

oCOMPARISON_OPERATOR = ‘GROUP’ / COMPARISON_ID = GROUP_CODE for a Location Group with an Action Level that applies to all locations in the group.

oCOMPARISON_OPERATOR = ‘DEFAULT’ / COMPARISON_ID = ‘DEFAULT’ to enter an Action Level for any location not included above.

If there is no Action Level specified for the unlisted locations, enter the word ‘NULL’ in the ACTION_LEVEL column for the DEFAULT row.

 

During the execution of the Action Level Exceedance II Report, for each PARAM_CODE sample result, the Advanced Action Level processor will:

Look for a matching SYS_LOC_CODE in the COMPARISON_ID column of the SINGLE rows
If not found:

oLook for a GROUP_CODE in the COMPARISON_ID column of the GROUP rows that contains the SYS_LOC_CODE
If not found:

Look for the DEFAULT row in the DT_ACTION_LEVEL_LOOKUP table

Once a match is found in the DT_ACTION_LEVEL_LOOKUP table, that row's ACTION_LEVEL, ACTION_LEVEL_MIN, WARNING_LEVEL and WARNING_LEVEL_MIN (if populated) are used as the location-specific action level, action level minimum, etc. for the sample result.

 

An example EDD with all three values populated is shown below.

 

15284-ex.edd.3.values

 

LOCATION

 

LOOKUP_SOURCE (25 char): If this action level is from an environmental agency, the agency 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 LOCATION.

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 not used in the LOCATION process.

COMPARISON_ID (20 char): The SYS_LOC_CODE of the location or GROUP_CODE of the location group for whom the location-specific action level will be processed.

COMPARISON_OPERATOR (10 char): This column is populated with SINGLE, GROUP or DEFAULT.

The SINGLE value is used when listing an action level for a single SYS_LOC_CODE. If a location is included in a SINGLE action level as well as a GROUP, the SINGLE value takes precedence.

The GROUP value is used when listing an action level for a Location Group.

The DEFAULT value is used to create a default action level if the sample's SYS_LOC_CODE does not match any of those listed.

COMPARISON_VALUE (19 char): This column is not used in the LOCATION process.

COMPARISON_UNIT (15 char): This column is not used in the LOCATION process.

ACTION_LEVEL (19 char): The value to use as the location-specific action level for the PARAM_CODE analyte.

ACTION_LEVEL_UNIT (15 char): The units for the location-specific action level.

ACTION_LEVEL_MIN (19 char): The value to use as the location-specific action level minimum for the PARAM_CODE analyte.

WARNING_LEVEL (19 char): The value to use as the location-specific warning level for the PARAM_CODE analyte.

WARNING_LEVEL_MIN (19 char): The value to use as the location-specific warning 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).

 

Notes:

1)The Action Level Exceedance II reports will only create one row for each sampling result that matches a given ACTION_LEVEL_CODE. Therefore, when a single location is in multiple location groups, with different action levels assigned to each group, there are two approaches that can be taken:

a.A separate ACTION_LEVEL_CODE can be created for each location group in order for each individual action level to be applied to a sample at that location. This will cause each sample to be listed multiple times in the report output (one time for each applicable ACTION_LEVEL_CODE).
or

b.More than one location group can be included in a single location-specific advanced action level in the DT_ACTION_LEVEL_LOOKUP table. Then, when a location is in two or more groups, the lowest ACTION_LEVEL and the highest ACTION_LEVEL_MIN will be used for that location's results.

2)Any values in the DT_ACTION_LEVEL_PARAMETER table's ACTION_LEVEL, ACTION_LEVEL_MIN, WARNING_LEVEL, and WARNING_LEVEL_MIN columns will be overwritten when a matching Advanced Action Level is found.