|
<< Click to Display Table of Contents >> Navigation: DQM > Configure & Manage DQM QAPPs > DQM Basic QAPP |
QAPP Name: DQM Basic QAPP (DQM BASIC_QAPP_v7_24_X.xlsx)
File Name: DQM_Basic_QAPP.xlsx
Dependencies: Licensed DQM and DQM Schema
Software: EQuIS DQM, EQuIS Professional EDP
•Gathering the Reference Values
Based on the USEPA 2020 National Functional Guidelines, the DQM Basic QAPP file was configured to allow users to more easily implement DQM in their EQuIS workflows by offering them a nearly complete QAPP EDD that requires minor reference value adjustments to use. The provided Basic QAPP EDD file (DQM BASIC_QAPP_v7_24_X.xlsx) follows the structure of the DQM_refvals.zip format, and once updated to match reference values in the target database, can be loaded with this format.
Note: The provided EDD Basic QAPP file is designed to necessitate review and update prior to loading. Several reference values will need to be aligned with the target database, including the analytical methods and matrices. The provided reason_code and qualifier values may be added without update, according to user preference. |
Watch the Office Hour recording of an overview of the Basic QAPP to learn more. The DQM Tools (files) described in the Office Hour are available by request on the EarthSoft Community Center (contact EarthSoft Support). Part of this package is a Word document that defines each file and describes the implementation process.
DQM Checks included in the EDD Basic QAPP file are listed below with a summary of their purpose. Details on the file review and update process can be found in the Updating QAPP File section.
•Global Parameters – While not a true check, Global Parameters handles and manages parameters that apply across the entire QAPP including information like batch types to evaluate, handling multiple data exceptions for a single result, identifying target data types, setting event and status settings, etc.
•Batch Completeness – Evaluates a batch of samples to determine completeness based on number and type of QC samples, number of non-lab samples, and analysis run duration.
•Dilution Factor – Evaluates if a sample/analyte was analyzed at a dilution (Dilution_Factor>1).
•Dissolved vs Total – Identifies instances where dissolved concentrations are greater than total concentrations.
•Estimated Results – Checks for sample results that fall between the method detection limit (MDL) and the reporting limit (RL).
•Field Blank Contamination – Assesses if any target analytes are detected in an associated field blank and qualifies results of associated samples if blank samples are contaminated.
•Holding Times – Assesses if the sample preparation and/or analysis holding time for a given analyte, method, matrix, and preservation status fall outside quality control limits.
•Lab Blank Contamination – Assesses if any target analytes are detected in an associated lab blank and qualifies results of associated samples if blank samples are contaminated.
•Percent Solids – Checks to determine if the percent solids value is within user-specified control and warning limits, where percent solids = 100 – DT_TEST.PERCENT_MOISTURE.
•Relative Percent Difference – Checks for Relative Percent Difference (RPD) values for duplicate sample results that fall outside QC limits. This check is run on the RPD between recoveries for matrix spike/matrix spike duplicate samples, laboratory control spike/laboratory control spike duplicate pairs, and laboratory duplicate pairs.
•Relative Percent Difference – Field – Checks for RPD values for field duplicate sample results that fall outside QC limits.
•Spike Recovery – Multiple Rules – Assesses if any of the spike compound recoveries are outside specified QC limits in field-collected and laboratory-generated spike samples. This check is performed on matrix spikes, matrix spike duplicates, blank spike, and blank spike duplicates.
•Surrogate Recovery – Assesses if any of the surrogate compound recoveries are outside specified QC limits.
•Trip Blank Contamination – Assesses if any target analytes are detected in an associated trip blank and qualifies results of associated samples if blank samples are contaminated.
The EDD sections and their general updates include:
•Format Information – Provides an overview of the structure of the file. This section is not loaded with the rest of the EDD.
•rt_holding_time_v2 – Requires updates to match medium_codes and analytic_methods.
•rt_dqm_control_limits_v2 – Requires updates to match matrix_codes, medium_codes and analytic_methods.
•rt_dqm_qapp – None of the fields in this section require an update, but can be adjusted to match preferred organizational vocabulary. Updates to the QAPP_code must be carried throughout the format.
•rt_dqm_check – No updates are necessary.
•rt_dqm_reason – None of the fields in this section require an update, but can be adjusted to match preferred organizational vocabulary. Any updates to this list should be carried to the related rt_dqm_rule records.
•rt_dqm_rule – None of the fields in this section require an update, but the remark and reason code fields can be adjusted to match preferred organizational vocabulary.
•rt_dqm_check_param – Requires updates to the param_value field at a minimum. Care should be taken reviewing and updating the cas_rn and param_value fields to ensure that instances where parameters call reference values, they match the valid values in the target database.
•rt_dqm_qualifier – None of the fields in this section require an update, but can be adjusted to match preferred organizational vocabulary. Any updates to the qualifier field must be carried to the rt_dqm_rule section.
•rt_dqm_check_param_type – None of the fields in this section require an update, but the remark field can be adjusted to match preferred organizational vocabulary.
•rt_matrix – This section allows users to add matrices/mediums to EQuIS based on QAPP requirements. The matrix_code and medium_code relationships must be defined for some DQM checks.
•rt_preservative – This section is populated based on common preservative abbreviations and methods. This table requires review and update to match existing valid values and available laboratory data in DT_TEST.PRESERVATIVE.
•rt_lookup – While no records are included with this EDD, adding records with a lookup_type = ‘dqm_event_status’ and a lookup_code with the desired unique status will allow users to track DQM events with the Event Status Tab.
Reference values in the DQM Basic QAPP EDD file must match reference values in the target database. Determine whether the QAPP will be available for use by many facilities or if the QAPP needs to be facility-specific.
An export of the reference value tables listed below filtered on STATUS_FLAG = 'A' is a good start in gathering the necessary information to update this QAPP.
•RT_SAMPLE_TYPE
•RT_RESULT_TYPE
•RT_TEST_BATCH_TYPE
•RT_MATRIX
•RT_MEDIUM
•RT_PRESERVATIVE
•RT_PREP_METHOD
•RT_ANALYTIC_METHOD
•RT_ANALYTE
Note that not all reference values may be applicable, particularly if the database contains diverse reference values for different projects/facilities.
If the EQuIS SQL form is available in the target database configuration, an EQuIS Professional user with EQuIS SQL form permissions and read permissions on the target facility can use the following scripts to query specific facilities for the relevant reference value data. If a QAPP is configured for use with a specific facility, ensure that the dqm_qapp_code and description values have been updated to refer to the facility.
RT_SAMPLE_TYPE
SELECT s.sample_type_code, COUNT(DISTINCT s.sample_type_code)
FROM dt_sample s
INNER JOIN rt_sample_type t ON s.sample_type_code = t.sample_type_code
WHERE s.facility_id = @facility_id AND t.status_flag = @status_flag
GROUP BY s.sample_type_code
RT_RESULT_TYPE
SELECT r.result_type_code, COUNT(DISTINCT r.result_type_code)
FROM dt_result r
INNER JOIN rt_result_type t ON r.result_type_code = t.result_type_code
WHERE r.facility_id = @facility_id AND t.status_flag = @status_flag
GROUP BY r.result_type_code
RT_TEST_BATCH_TYPE
SELECT b.test_batch_type, COUNT(DISTINCT b.test_batch_type)
FROM dt_test_batch b
INNER JOIN rt_test_batch_type t ON b.test_batch_type = t.test_batch_type
WHERE b.facility_id = @facility_id AND t.status_flag = @status_flag
GROUP BY b.test_batch_type
RT_MATRIX
SELECT s.matrix_code, s.facility_id, COUNT(DISTINCT s.matrix_code)
FROM dt_sample s
INNER JOIN rt_matrix m ON s.matrix_code = m.matrix_code
WHERE s.facility_id = @facility_id AND m.status_flag = @status_flag
GROUP BY s.matrix_code, s.facility_id
RT_MEDIUM
SELECT s.matrix_code, s.facility_id, m.medium_code, COUNT(DISTINCT s.matrix_code)
FROM dt_sample s
INNER JOIN rt_matrix m ON s.matrix_code = m.matrix_code
WHERE s.facility_id = @facility_id AND m.status_flag = @status_flag
GROUP BY s.matrix_code, s.facility_id, m.medium_code
RT_PRESERVATIVE
SELECT t.preservative, COUNT(DISTINCT t.preservative)
FROM dt_test t
INNER JOIN rt_preservative m ON t.preservative = m.preservative
WHERE t.facility_id = @facility_id AND m.status_flag = @status_flag
GROUP BY t.preservative
RT_PREP_METHOD
SELECT t.prep_method, COUNT(DISTINCT t.prep_method)
FROM dt_test t
INNER JOIN rt_prep_method m ON t.prep_method = m.prep_method
WHERE t.facility_id = @facility_id AND m.status_flag = @status_flag
GROUP BY t.prep_method
RT_ANALYTIC_METHOD
SELECT t.analytic_method, t.facility_id, COUNT(DISTINCT t.analytic_method)
FROM dt_test t
INNER JOIN rt_analytic_method a ON t.analytic_method = a.analytic_method
WHERE t.facility_id = @facility_id AND a.status_flag = @status_flag
GROUP BY t.analytic_method, t.facility_id
RT_ANALYTE
SELECT r.cas_rn, r.facility_id, COUNT(DISTINCT r.cas_rn)
FROM dt_result r
INNER JOIN rt_analyte a ON r.cas_rn = a.cas_rn
WHERE r.facility_id = @facility_id AND a.status_flag = @status_flag
GROUP BY r.cas_rn, r.facility_id
Rename the DQM BASIC_QAPP_v7_24_X.xlsx file as desired to meet user needs. Modify values within the DQM Basic QAPP EDD file to match the target database and meet user needs.
The rt_dqm_control_limits_v2 section of the DQM_refvals format governs several checks, but for the purposes of this Basic QAPP, only three columns need to be updated to match the target database reference values.
•matrix_code – Update the corresponding general values to match at least one similar matrix type from the valid values in RT_MATRIX.MATRIX_TYPE.
Note: Individual records for each possible matrix are not necessary if the control limit requirements remain consistent across the medium_code and the corresponding medium_code field has been updated. |
•analytic_method – Update the descriptive placeholder methods to match the appropriate analytic methods based on valid values in RT_ANALYTIC_METHOD.ANALYTIC_METHOD.
•medium_code – Confirm or update the placeholder values to match the valid values in RT_MEDIUM.MEDIUM_CODE.
The included checks are listed below with details on the parameters and settings that should be updated to match the target database. Click the link to navigate to the related article for further information on the check logic.
rt_dqm_check_param Section
•result_type_code – Match the parameter to the target reference values associated with the results that should be reviewed by DQM.
•nondetect_detection_limit – Review DT_RESULT and choose a detection limit field that is reliably populated and is appropriate to identify as the non-detect limit (REPORTING_DETECTION_LIMIT, METHOD_DETECTION_LIMIT, or QUANTITATION_LIMIT).
•test_batch_type – Choose the batch type(s) that will filter the DQM Start Event query.
rt_dqm_check_param Section
•non_qc_sample_type – List all non-QC sample types in the target dataset based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•required_qc_samples – Adjust the listed QC sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•sample_type_code – Adjust the listed sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•result_type_code – Adjust the listed result type to match the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code – Adjust the listed result type to match the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_type_code – Adjust the listed sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•dissolved_fraction – Update the value to match the dissolved fraction represented in RT_FRACTION.FRACTION.
•total_fraction – Update the value to match the total fraction represented in RT_FRACTION.FRACTION.
•detection_limit – Review DT_RESULT and choose a detection limit field that is reliably populated and is appropriate to identify as the non-detect limit (REPORTING_DETECTION_LIMIT, METHOD_DETECTION_LIMIT, or QUANTITATION_LIMIT) for use in the calculation absolute difference rules of the check [DT_RESULT.RESULT_NUMERIC >= multiplier * DT_RESULT.REPORTING_DETECTION_LIMIT].
•sample_types – Adjust the listed sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•result_types – Adjust the listed result type to match the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
rt_dqm_check_param Section
•estimated_detection_limit – Review the DT_RESULT table and choose a detection limit field that is reliably populated and is appropriate to compare against the method detection limit (REPORTING_DETECTION_LIMIT or QUANTITATION_LIMIT).
•result_type_code – Adjust the listed result type to match the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_type_code – Adjust the listed sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code – Adjust the listed result type to match the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_type_code – Adjust the listed sample types to match the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_holding_time_v2 Section
•analytic_method – Update the descriptive placeholder methods to match the appropriate analytic methods based on valid values in RT_ANALYTIC_METHOD.ANALYTIC_METHOD.
•medium_code – Update to match the appropriate medium codes based on the valid values in RT_MATRIX.MEDIUM_CODE.
•dqm_preserved_yn – If preservation data are not provided in the laboratory EDDs and holding time intervals are not impacted by preservative state, this value does not need to be changed. If the holding time criteria will differ based on preservative state (in DT_TEST.PRESERVATIVE), refer to the check documentation for this parameter for more details.
•preservative – Update the provided preservatives to match their corresponding valid values based on RT_PRESERVATIVE.PRESERVATIVE.
rt_dqm_check_param Section
•result_type_code – Match the parameter to the target reference values in RT_RESULT_TYPE.RESULT_TYPE_CODE associated with the results that should be reviewed by the check.
•normal_sample_types – Match the appropriate sample types that should be evaluated in this check based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•AmbientBlankSampleType – Adjust the listed sample types to match the ambient blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•EquipmentBlankSampleType – Adjust the listed sample types to match the equipment blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•FieldBlankSampleType – Adjust the listed sample types to match the field blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code – Match the parameter to the target reference values in RT_RESULT_TYPE.RESULT_TYPE_CODE associated with the results that should be reviewed by the check.
•normal_sample_types – Match the appropriate sample types that should be evaluated in this check based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•LabBlankSampleType – Adjust the listed sample types to match the lab blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•MethodBlankSampleType – Adjust the listed sample types to match the method blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code – Match the parameter to the target reference values in RT_RESULT_TYPE.RESULT_TYPE_CODE associated with the results that should be reviewed by the check.
•normal_sample_types – Match the appropriate sample types that should be evaluated in this check based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•AmbientBlankSampleType – Adjust the listed sample types to match the ambient blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•TripBlankSampleType – Adjust the listed sample types to match the trip blank valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code – Match the parameter to the target reference values in RT_RESULT_TYPE.RESULT_TYPE_CODE associated with the results that should be reviewed by the check.
•sample_type_codes – Match the appropriate sample types that should be evaluated in this check based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•medium_code_soil – Update to match the soil medium code based on the valid values in RT_MATRIX.MEDIUM_CODE.
•sample_result_type_1 – Match the appropriate result type that correspond to the laboratory control spike and laboratory control spike duplicate samples in the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_result_type_2 – Match the appropriate result type that correspond to the matrix spike and matrix spike duplicate samples in the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_result_type_3 – Match the appropriate result type that correspond to the laboratory control spike and laboratory control spike duplicate samples in the valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_type_LCSD – Match the appropriate sample types that match the laboratory control spike duplicate sample valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•sample_type_MSD – Match the appropriate sample types that match the matrix spike duplicate valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•sample_type_Orig_Dup – Match the appropriate sample types that match the duplicate valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
Relative Percent Difference – Field
rt_dqm_check_param Section
•medium_code_soil – Update to match the soil medium code based on the valid values in RT_MATRIX.MEDIUM_CODE.
•medium_code_water – Update to match the water medium code based on the valid values in RT_MATRIX.MEDIUM_CODE.
•sample_type_Field_Dup – Match the appropriate field duplicate sample types that should be evaluated in this check based on the valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•result_type_code – Match the parameter to the target reference values in RT_RESULT_TYPE.RESULT_TYPE_CODE associated with the results that should be reviewed by the check.
rt_dqm_check_param Section
•result_type_code_spike – Match the appropriate result type that corresponds to the spike compound valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
•sample_type_blank_spike – Match the appropriate sample types that match the laboratory control spike duplicate sample valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
•sample_type_matrix_spike – Match the appropriate sample types that match the matrix spike duplicate valid values in RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.
rt_dqm_check_param Section
•result_type_code_surrogate – Match the appropriate result type that corresponds to the surrogate valid values in RT_RESULT_TYPE.RESULT_TYPE_CODE.
Once updated to match the target database reference value fields, this QAPP EDD file can be loaded to the database via Professional EQuIS Data Processor (EDP) using the DQM-refvals format. Ensure that the QAPP EDD file does not contain errors. Using the Insert & Merge commit type will allow any new information to be loaded to the target database without impact to existing DQM reference values, if present.
Copyright © 2024 EarthSoft, Inc. • Modified: 11 Dec 2024