Spike Recovery Check

Overview

The DQM Check for Spike Recovery will qualify results if spike sample recoveries fall outside Quality Control (QC) limits.

Notes:

•Samples are considered duplicate samples if the Sample Type ends with a 'D'

•The QC_SPIKE_RECOVERY column or both of the QC_SPIKE_ADDED and QC_SPIKE_MEASURED columns must be populated in DT_RESULT_QC for this check to function.

•If the sample is a duplicate sample, then DQM will look first in QC_DUP_SPIKE_RECOVERY (or in both QC_DUP_SPIKE_ADDED and QC_DUP_SPIKE_MEASURED). If those columns do not contain data, then QC_SPIKE_RECOVERY (or both QC_SPIKE_ADDED and QC_SPIKE_MEASURED) are checked.

•Control limits for this check are determined as described in the RT_DQM_CONTROL_LIMITS section of the DQM_CONTROL_LIMITS help article.

Check

Check name is EarthSoft.DQM.SpikeRecovery2 = "Spike Recovery - Multiple Rules".

Parameters

This check uses four Global Parameters:

1.msd_parent_sample_ms – See Sample Relationships section below for additional information.

2.reportable_result – DT_RESULT.REPORTABLE_RESULT values to include in DQM datasets.

3.result_type_code – DT_RESULT.RESULT_TYPE_CODE to include in DQM datasets.

4.test_type_batch – AT_TEST_BATCH_ASSIGN.TEST_BATCH_TYPE to include in DQM dataset.

Fourteen parameters are available for this check. None of these parameters allow for input of analyte-specific or method-specific PARAM_VALUEs.

1.acceptable_variance_LCS – Defaults to 1.

2.acceptable_variance_MS – Defaults to 1.

3.calculated_before_reported_LCS – Defaults to “N”.

4.calculated_before_reported_MS – Defaults to “N”.

5.compare_to_calculated_LCS – Defaults to “N”.

6.compare_to_calculated_MS – Defaults to “N”.

7.dilution_factor_control_limit – Control limit for comparison of each result's dilution factor. Results with a dilution factor over the control limit will not be evaluated. Defaults to 5.

8.flag_QC – TRUE/FALSE for flagging QC samples; QC sample types are configured in the sample_type_blank_spike and sample_type_matrix_spike parameters.

9.Multiplier – Defaults to 4.

10.inorganics_asso_method – test_batch/parent_sample for associating inorganic results. Defaults to "test_batch".

11.organics_asso_method – test_batch/parent_sample for associating organic results. Defaults to "test_batch".

12.result_type_code_spike – Defaults to “SC”.

13.sample_type_blank_spike – Defaults to “BS,BD”.

14.sample_type_matrix_spike – Defaults to “FS,MS,MSD,SD”.

Additional Notes for Parameters

1.If compare_to_calculated = "Y" or calculated_before_reported = "Y" then a Spike Recovery will be calculated for every spike compound using the data populated in the qc_spike_added and qc_spike_measured columns.

2.If compare_to_calculated = "Y" then

a.DQM will compare the calculated recovery to the recovery populated in the QC_RECOVERY column.

b.DQM will create an error message in the Errors tab if the difference between the calculated recovery and the reported recovery exceeds the acceptable_variance.

3.If calculated_before_reported = "Y" then

a.DQM will use the calculated value to compare to the Control Limit.

b.If the calculated value is not available, DQM will use the reported recovery value to compare to the Control Limit.

c.If the Control Limit is exceeded, an Exception will be added to the Exceptions table.

Notes:

•Default parameter values listed above refer to the value provided in the DQM Starter QAPP.xlsx.

•If there is no value for LOW_CUTOFF, then LOW_CUTOFF is set to zero and the third rule in each foursome of rules below is effectively eliminated.

Rules

There are twelve rules:

Rule #	Result Types	Rule Description	DQM Rule
1	Blank Spikes	% Recovery > Upper Cut Off	BLANK SPIKE > UPPER_CUTOFF
2		% Recovery > Upper Control Limit (UCL)	BLANK SPIKE > UCL
3		Lower Control Limit (LCL) > % Recovery ≥ Low Cutoff	LCL > BLANK > LOW_CUTOFF
4		% Recovery < Low_Cutoff	BLANK SPIKE < LOW_CUTOFF
5	Inorganic Spikes	% Recovery > Upper Cut Off	INORGANIC SPIKE > UPPER_CUTOFF
6		% Recovery > UCL	INORGANIC SPIKE > UCL
7		LCL > % Recovery ≥ Low_Cutoff	LCL > INORG > LOW_CUTOFF
8		% Recovery ≤ Low_Cutoff	INORGANIC SPIKE < LOW_CUTOFF
9	Organic Spikes	% Recovery > Upper Cut Off	ORGANIC SPIKE > UPPER_CUTOFF
10		% Recovery > UCL	ORGANIC SPIKE > UCL
11		LCL > % Recovery ≥ Low_Cutoff	LCL > ORG > LOW_CUTOFF
12		% Recovery < Low_Cutoff	ORGANIC SPIKE < LOW_CUTOFF

Additional Notes for the Result Types:

Blank Spikes – Review results with:

•DT_SAMPLE.SAMPLE_TYPE_CODE = sample_type_blank_spike, and

•DT_RESULT.RESULT_TYPE_CODE = result_type_code_spike.

Inorganic Spikes – Review results with:

•DT_SAMPLE.SAMPLE_TYPE_CODE = sample_type_matrix_spike,

•DT_RESULT.RESULT_TYPE_CODE = result_type_code_spike, and

•RT_ANALYTE.ORGANIC_YN = 'N'.

Organic Spikes – Review results with:

•DT_SAMPLE.SAMPLE_TYPE_CODE = sample_type_matrix_spike,

•DT_RESULT.RESULT_TYPE_CODE = result_type_code_spike, and

•RT_ANALYTE.ORGANIC_YN = 'Y'.

Use of Reason Codes with the rules is discussed here.

Calculations (where table is not indicated, it is DT_RESULT_QC)

Notes:

•For the calculations below and for DQM Version 7.21.1 and earlier versions, ALL fields mentioned in the RT_DQM_CONTROL_LIMITS must be populated and numeric or the DT_RESULT_QC or default value will be used as specified below. For example, if only RT_DQM_CONTROL_LIMITS.LCS_REC_LOWER_CUTOFF is populated for the Blank Spike, the default value of 0 will be used for the spike_lco.

•The RT_DQM_CONTROL_LIMITS.METHOD_ANALYTE_GROUP_CODE must be null or it must be a dataset selection on the Start Event Form.

RT_DQM_CONTROL_LIMITS is discussed in more detail here.

•If QC_ORIGINAL_CONC > multiplier * QC_SPIKE_ADDED, then skip result.

•If QC_SPIKE_RECOVERY is Numeric,

▪then spikeRecovery = QC_SPIKE_RECOVERY

▪else spikeRecovery for blank spikes = 100 * QC_SPIKE_MEASURED / QC_SPIKE_ADDED and for matrix spikes = 100 * ((QC_SPIKE_MEASURED-QC_ORIGINAL_CONC) / QC_SPIKE_ADDED)

•For Spike Duplicates, if regular columns are NULL, then if QC_DUP_SPIKE_RECOVERY is Numeric,

▪then spikeRecovery = QC_DUP_SPIKE_RECOVERY

▪else spikeRecovery for blank spike duplicate = 100 * QC_DUP_SPIKE_MEASURED / QC_DUP_SPIKE_ADDED and for matrix spike duplicate = ((QC_DUP_SPIKE_MEASURED-QC_ORIGINAL_CONC) / QC_DUP_SPIKE_ADDED)

For Blank Spikes:

If any of the below values are numeric, the following RT_DQM_CONTROL_LIMIT fields will be used:

•spike_lco = RT_DQM_CONTROL_LIMITS.LCS_REC_LOWER_CUTOFF

•spike_lcl = RT_DQM_CONTROL_LIMITS.LCS_RECOVERY_LCL

•spike_ucl = RT_DQM_CONTROL_LIMITS. LCS_RECOVERY_UCL

•spike_upper_cutoff = RT_DQM_CONTROL_LIMITS. LCS_REC_UPPER_CUTOFF

When the value above is not populated:

•spike_lco = 0.0

•spike_lcl = QC_SPIKE_LCL

•spike_ucl = QC_SPIKE_UCL

•spike_upper_cutoff = 999.99

For Matrix Spikes (Inorganic and Organic):

If any of the below are numeric, the following RT_DQM_CONTROL_LIMIT fields will be used:

•spike_lco = RT_DQM_CONTROL_LIMITS.MS_REC_LOWER_CUTOFF

•spike_lcl = RT_DQM_CONTROL_LIMITS.MS_RECOVERY_LCL

•spike_ucl = RT_DQM_CONTROL_LIMITS.MS_RECOVERY_UCL

•spike_upper_cutoff = RT_DQM_CONTROL_LIMITS.MS_REC_UPPER_CUTOFF

When the value above is not populated:

•spike_lco = 0.0

•spike_lcl = QC_SPIKE_LCL

•spike_ucl = QC_SPIKE_UCL

•spike_upper_cutoff = 999.99

Associations

•For organic results:

•When organics_asso_method = test_batch, associated results are found using TEST_BATCH_ID.

•When organics_asso_method = parent_sample, associated results are found using the PARENT_SAMPLE_CODEs. For Matrix Spike and Matrix Spike Duplicate samples, parent samples are based on the Global Parameter setting msd_parent_sample_ms.

•For inorganic results:

•When inorganics_asso_method = test_batch, associated results are found using TEST_BATCH_ID.

•When inorganics_asso_method = parent_sample, associated results are found using the PARENT_SAMPLE_CODEs. For Matrix Spike and Matrix Spike Duplicate samples, parent samples are based on the Global Parameter setting msd_parent_sample_ms.

•Associated results must also match the FRACTION of the flagged Spike result.

Sample Relationships

•When the global parameter msd_parent_sample_ms = “Y”.

oThe PARENT_SAMPLE_CODE for Matrix Spike Duplicate (MSD) sample should match the Matrix Spike (MS) SYS_SAMPLE_CODE.

oThe PARENT_SAMPLE_CODE for the MS sample should match the original sample’s SYS_SAMPLE_CODE.
Although the parent for MSD samples are MS, the result populated in DT_RESULT_QC.QC_DUP_ORIGINAL_CONC should equal the original sample’s result value.

•When the global parameter msd_parent_sample_ms = “N”.

oThe PARENT_SAMPLE_CODE for both the MSD and MS should match the original sample’s SYS_SAMPLE_CODE.

Exceptions (see Calculations for definitions):

•If spikeRecovery < spike_lco, then qualify spike result and any associated result.

•If spike_lcl > spikeRecovery ≥ spike_lco, then qualify spike result and any associated result.

oIf spikeRecovery > spike_ucl, then qualify spike result and any associated result with a detected concentration.

An example remark saved to DT_RESULT_DQM_REMARK for an exception is:

"Associated Result from : The Blank Spike Recovery is less than the Lower Cutoff."

Errors

Where table is not indicated, it is DT_RESULT_QC.

•If QC_SPIKE_RECOVERY, QC_SPIKE_ADDED, and QC_SPIKE_MEASURED are all NULL or Not Numeric, then add error and skip result.

•If QC_SPIKE_RECOVERY is NULL or Not Numeric, and either QC_SPIKE_ADDED or QC_SPIKE_MEASURED are also NULL or Not Numeric, then add error and skip result.

•For Spike Duplicates, if regular columns are NULL, apply the same error checking to QC_DUP_SPIKE_RECOVERY, QC_DUP_SPIKE_ADDED, and QC_DUP_SPIKE_MEASURED.

•If the Upper Control Limit is NULL or Not Numeric in both RT_DQM_CONTROL_LIMITS and DT_RESULT_QC, then add error and skip result.

•If the Lower Control Limit is NULL or Not Numeric in both RT_DQM_CONTROL_LIMITS and DT_RESULT_QC, then add error and skip result.

•If compare_to_calculated = Y and the variance between the reported and calculated recoveries exceed the acceptable_variance, then add error and skip result

•If ORIGINAL_CONC exceeds multiplier * QC_SPIKE_ADDED, then add warning and skip result.

•Dilution Factor exceeded the Dilution Factor Control Limit.

Example error messages:

•"One of the required rules for this check is not present in the RT_DQM_RULE table."
If you receive this error, the QAPP can be updated following the How to Update Existing QAPPs documentation page.

•“Spike Duplicate Recovery data missing from DT_RESULT_QC for either {QC_SPIKE_RECOVERY} or {QC_SPIKE_ADDED} and {QC_SPIKE_MEASURED}.”

•“The variance between the reported (90) and the calculated (100) Spike Recovery exceeded the Acceptable Variance (2). at sys_sample_code=BS_1_AQ_UCO_TEST and analytic_method='SW8260' and cas_rn='100-41-4'”

•"Spike Recovery {FIELDNAME} is missing or not numeric;_".

•This check was skipped for TEST_ID = '2622720' because the Dilution Factor (10) exceeded the Dilution Factor Control Limit (5)

Warnings

In the Review Exceptions by Check Tab, the following may appear as warnings:

•6 results were NOT processed for Matrix Spike Recovery due to NULL value in the ORGANIC_YN column of the RT_ANALYTE table for '<CAS_RN>' analyte

•Matrix Spike Recovery Check - Skipped Because Original Concentration exceeds Multiplier * Spike Added