EQuIS supports location-specific monthly average 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 the Advanced Action Levels Setup 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
•Using Locations in DT_ACTION_LEVEL_LOOKUP
•MAG Sum and TEQ Sum in DT_ACTION_LEVEL_LOOKUP
Creation of Location-Specific Monthly Average Action Level Using LOCATION and LOCATION_HIST Methods
The Monthly Average AAL performs the following steps:
1.It calculates the average value for each CAS_RN/SYS_LOC_CODE combination in a group of results from the ALEII report output (selected one of two ways as described below in the section on LOOKUP_METHOD).
2.It adds a new row to the ALEII report output to hold the result of each Monthly Average calculation.
3.It populates the AL_RESULT_TEXT and AL_RESULT_VALUE column of the new row added in Step 2 with the calculated values from Step 1.
4.It then finds the appropriate Action Level for that CAS_RN/SYS_LOC_CODE combination and performs the Action Level Exceedance comparison as specified by the ALEII report parameter selections.
To create a Location-specific Monthly Average Action Level, follow the general setup steps in the Advanced Action Levels Setup help article and populate the DT_ACTION_LEVEL_LOOKUP table as described below.
Instructions for populating the DT_ACTION_LEVEL_LOOKUP table:
•PARAM_CODE = CAS_RN for each analyte to be processed
•LOOKUP_METHOD = ‘LOCATION’ or ‘LOCATION_HIST’
oLOCATION
•calculates the monthly average value for each CAS_RN/SYS_LOC_CODE combination for the month (28, 29, 30 or 31 days depending on the month) previous to the latest sample date in the ALEII report output
•creates one new row in the ALEII output for that CAS_RN/SYS_LOC_CODE combination
•populates AL_RESULT_TEXT and AL_RESULT_VALUE with the calculated Monthly Average value
•populates SAMPLE_NAME with “Mon_Avg_Begin_dd_MMM_yyyy” and SYS_SAMPLE_CODE with “Mon_Avg_End_dd_MMM_yyyy”
•finds the matching row in DT_ACTION_LEVEL_LOOKUP for that CAS_RN/SYS_LOC_CODE combination and populates ACTION_LEVEL and ACTION_LEVEL_MIN (if available) in the new row
•performs the Action Level Exceedance comparison as specified by the ALEII report parameter selections
oLOCATION_HIST
•calculates the monthly average value for each CAS_RN/SYS_LOC_CODE combination for every calendar month in the ALEII report’s date range
•creates a new row in the ALEII output for each CAS_RN/SYS_LOC_CODE combination and each of those calendar months
•populates AL_RESULT_TEXT and AL_RESULT_VALUE in each new row with the calculated Monthly Average values
•populates SAMPLE_NAME with “xx_Mon_Avg_Begin_dd_MMM_yyyy” and SYS_SAMPLE_CODE with “xx_Mon_Avg_End_dd_MMM_yyyy” where “xx” is the number of the month in the sequence of months from the report output (this allows the new Monthly Average rows to be sorted properly)
•finds the matching row in DT_ACTION_LEVEL_LOOKUP for that CAS_RN/SYS_LOC_CODE combination and populates ACTION_LEVEL and ACTION_LEVEL_MIN (if available) in each new row
•performs the Action Level Exceedance comparison as specified by the ALEII report parameter selections
•COMPARISON_GROUP / COMPARISON_ID combinations as follows:
oCOMPARISON_GROUP = ‘1’ / COMPARISON_ID = SYS_LOC_CODE for an individual location with its own unique Action Level
oCOMPARISON_ GROUP = ‘2’ / COMPARISON_ID = GROUP_CODE for a Location Group with an Action Level that applies to all of the locations in the group
oCOMPARISON_ GROUP = ‘0’ / 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. This will also eliminate the addition of Monthly Average rows for that CAS_RN.
•COMPARISON_OPERATOR = ‘MON_AVG’ or ‘MON_AVG_H’
oMON_AVG
•calculates the monthly average using REPORT_RESULT_VALUE from every included result for that CAS_RN
oMON_AVG_H Performs the same function as the location monthly averages, but only includes data in the average that pass a certain criteria. This criteria is established in the CONSTANT_1 column for a different analyte (CAS_RN listed in the CALC_METHOD column).
•For each sample, finds the value for the CAS_RN listed in the CALC_METHOD column (typically HARDNESS or pH)
•If that value is less than the limit listed in the CONSTANT_1 column, then that sample is not included in the monthly average calculation
•If there is no result for the CAS_RN listed in the CALC_METHOD column, then that sample is not included in the monthly average calculation
•Using only the included samples, calculates the monthly average using REPORT_RESULT_VALUE for that CAS_RN listed in PARAM_CODE.
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. (See Note #1 below for how to handle more than one Location Group).
oIf 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 and ACTION_LEVEL_MIN (if populated) are used as the action level and action level minimum for the monthly average value calculated as explained above.
•(Note that warning levels are not supported for the Monthly Average functions of this advanced action level at this time.)
An example EDD is shown below.
LOCATION and LOCATION_HIST
•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 LOCATION or LOCATION_HIST.
•PARAM_CODE (CAS_RN) (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 indicates whether the COMPARISON_ID is for a single SYS_LOC_CODE or a GROUP_CODE as described above.
•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 MON_AVG or MON_AVG_H. The different calculations are described above.
•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.
•CALC_METHOD (255 char): This column is used for a different purpose in the Monthly Average AAL. This column will hold the CAS_RN for the MON_AVG_H funtion. The value must match the appropriate CAS_RN exactly.
•CONSTANT_1 (19 char): This column holds the limit to be applied to the results for the CAS_RN in the CALC_METHOD column, as described above for the MON_AVG_H function.
•REMARK (2000 char): A remark that will be placed in ACTION_LEVEL_NOTE. (only 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). 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 locations 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 over-written when a matching Advanced Action Level is found.
|