Processing Data with a DQM Event
Copyright © 2019 EarthSoft, Inc • Modified: 04 Mar 2019
When the Create DQM Event process finishes, the DQM Event Review form opens and displays results in a series of tabs:
•Review Exceptions by Check,
•Review All Results,
•Review Samples and Results by Sample, and
The Event Summary tab displays a summary of the DQM, the number of results evaluated, and the number of exceptions to the checks. Qualifiers and remarks can be revised on the Review Exceptions by Check, Review All Results, and Review Results by Sample tabs.
Note: If errors are encountered during the creation of an Event, the Errors tab is displayed first.
During the Create Event step, DQM adds several new columns to the Analytical Results output table, including DQM_QUALIFIER, DQM_REMARK, APPLY_QUALIFIER and APPLY_REMARK. When DQM discovers an exception to a DQM Rule, both QUALIFIER columns are populated with that Rule's qualifier. The DQM_QUALIFIER and DQM_REMARK columns cannot be edited (to preserve a record of the DQM suggested qualifier).
Once the DQM event is created, data validators can review the DQM generated flags by check, sample, or other filter. The APPLY_QUALIFIER column and the APPLY_REMARK column can be edited on the DQM Event Review form. Any in-process events can be saved for future reference and can be edited at a later time. These changes are stored in a variety of DQM tables in the EQuIS database but changes to other data tables does not occur until the DQM event is complete. When the DQM Event is Complete, the APPLY_QUALIFIER column is saved to the VALIDATOR_QUALIFIER and/or INTERPRETED_QUALIFIER columns in DT_RESULT according to the options selected.
Note: An Event must be Saved before it can be Processed and/or Completed.
When the Event Review Form first opens, the Save Event button is red and there is a reminder to "Review the new Event and Save it before making any changes."
If after reviewing the results in the newly created Event, the user does not want to Save the Event, click the Back Arrow button, and then click Yes when asked about leaving without saving.
After clicking the Save Event button, the user will be prompted for a DQM Event Name. A suggested event name will populate the textbox based on the DEFAULT_EVENT_NAME Global Parameter:
•If "DateTime", then "DQM - " plus the current date and time.
•If "Selection", then "DQM - " plus the dataset selection(s) made to create the event.
The suggested event name can be accepted as is or edited.
Clicking the OK button will save the event and the results of the Save operation will be displayed.
•A Global Parameter, DEFAULT_EVENT_NAME, will determine if the suggested Event Name will be based on the DateTime or on the Selection made during the Create Event step. If the parameter setting is Selection and there are many selections of a single input type (e.g., Sample), the default name will include all and may require truncation.
•The maximum length of an Event Name is 64 characters. If a name longer than 64 characters is entered, an explanatory error message will pop up and the user will be given the chance to reenter the name.
The DQM Event elements are saved to the following tables so that they can be recalled when the Event is reopened:
•The selections made during the Create an Event step:
▪ST_USER_REPORT – The USER_REPORT_ID is auto-generated during this Save step.
▪ST_USER_REPORT_PARAMETER – Each selection on the Create Event screen is saved as a report parameter.
•The Event Summary:
▪Each row in the Event Summary is saved as a record in the DT_NOTE table.
▪The USER_REPORT_ID from above is stored in the DB_RECORD_ID column to allow these records to be retrieved.
▪All of the exceptions and associated results are stored in DT_RESULT_DQM.
▪The Created Date of the Event is stored in the DQM_DATE column to allow these records to be retrieved.
▪The DQM suggested qualifier is saved in the DT_RESULT_DQM.QUALIFIER column.
▪The APPLY_QUALIFIER and APPLY_REMARK are saved in the DT_RESULT_DQM columns of the same name.
▪The EX_OR_ASSO column tracks whether the result was an exception or an associated result.
▪The EUID of every result in the Event is stored in the RESULT_EUID column of AT_DQM_RESULT.
▪The USER_REPORT_ID is stored in the DQM_USER_REPORT_ID column to allow these records to be retrieved.
These values are saved in the above tables when the Event is saved, which allow an Event to be Reopened. DT_RESULT is not updated until the Event is Completed.
To distinguish from the "DQM Exceptions by EDD" User Reports, the ST_USER_REPORT.STATUS_FLAG of DQM Event User Reports is "D".
After the Event has been Saved, the Save button will change to black lettering, and the DQM Event ID (ST_USER_REPORT.NAME) will be displayed.
Any non-fatal errors that were encountered during the Create Event process will be displayed In the Errors tab. When errors are encountered during the creation of an event, the Errors tab is displayed first. If errors are denoted on the Errors tab, resolve the errors before proceeding to the next steps. Once the needed changes are made to resolve the errors, the DQM event will need to be re-created.
If errors are listed, the suggested workflow is:
1.Close the event without saving.
2.Repair problems with dataset identified in the errors.
3.Recreate the event.
If an Event is saved regardless of errors, the particular error messages are not saved.
Review the Event Summary tab to determine if DQM found any exceptions to the DQM checks applied to the data set. The Event Summary tab displays the number of results reviewed and a row for each check that was run with the following columns:
1.Check Name – In addition to the names of the listed checks, this column also includes the row header for some additional information such as the DQM QAPP Code, Holding Time Types, MAG Code, Unvalidated Only Option, and Field Quality Comment option.
2.Type of Sample or Result Reviewed (by the Check) – For the checks, this column lists the values from the DQM Settings used to select results for review by that check.
a.For example, the Relative Percent Difference Field Check looks for "S = FD" (i.e., DT_SAMPLE.SAMPLE_TYPE_CODE = 'FD') as specified by the sample_type_Field_Dup check parameter.
b.Another example is the Surrogate Recovery Check that looks for "R = SUR" (i.e., DT_RESULT.RESULT_TYPE_CODE = 'SUR') as specified by the result_type_code_surrogate check parameter.
c.For the other types of rows (DQM QAPP Code, etc. listed above) this column displays the value of that particular row header.
d.If there were errors when the event was created, the number of errors is listed.
3.Total Number of Results – The total number of results retrieved to create the event dataset.
a.The dataset will always include all of the results from the selected group (e.g., DT_SAMPLE.TASK_CODE = 'XYZ') with DT_RESULT.RESULT_TYPE_CODE = DQM.GLOBAL_PARAMETERS.RESULT_TYPE_CODE (defaults to 'TRG') and DT_RESULT.REPORTABLE_RESULT = DQM.GLOBAL_PARAMETERS.REPORTABLE_RESULT (defaults to 'Yes, Y').
b.Including some checks in the event will add to the total number of results in the Event dataset. For example, including the Surrogate Recovery Check will also include results with RESULT_TYPE_CODE = DQM.SURROGATE_RECOVERY.RESULT_TYPE_CODE_SURROGATE (defaults to 'SUR').
4.Number of Results Reviewed (by the Check) – The number of results in the Event dataset that were processed by this check.
a.When the Type of Result Reviewed column is All Results, this will be the same number as the Total Number of Results.
b.When the check lists a specific type of sample or result to be reviewed, then this will be the number of results with that type of sample or result.
5.Number of Exceptions – The number of results that failed the check criteria (e.g., a result from a trip blank sample with a detected concentration).
6.Method used to Find Associated Results – When Exceptions are found, DQM then looks for associated results using the method listed here (e.g., a matching Test ID, Test Batch ID, or SDG). The method used to find associated results is different from check to check.
7.Number of Results Associated – The number of regular results that were qualified due to their association with the Exception result using the method listed.
•When changing any of the Apply Qualifiers and/or Apply Qualifier Remarks, Save is required before leaving that tab (or lose the changes).
•Changed values in the Apply Qualifier column are saved in the DT_RESULTS_DQM table when the event is Saved, but they are not "applied" to the qualifier column in DT_RESULT until the event is completed.
•Only values with STATUS_FLAG = 'A' in RT_QUALIFIER can be entered in the Apply Qualifier column by any of the methods below. The Apply Qualifier can be updated by:
oEntering a value in the yellow-background text box under the Update Q button and clicking the Update Q button, or
oEntering a new value directly into the Apply Qualifier column of a single result by selecting a value from the RT_QUALIFIER drop-down list in that column.
•The values in the Lab, Validator, and/or Interpreted Qualifier columns and/or the existing value in the Apply Qualifier column itself can be included in the Updated Apply Qualifier column by checking the check box over the Qualifier columns to include any values in those columns when the Update button is clicked.
oIf a letter is included in two or more of the Included qualifier columns, it will only be included once in the Apply Qualifier.
oIf the yellow entry field is NULL, the values in any checked qualifier columns will still be included in the Apply Qualifier column.
•If the Apply Q check box is left unchecked, clicking the Update Apply Q button overwrites the existing values with whatever is in the yellow entry field. This includes clearing any values if the yellow entry field is NULL.
•The Apply Remark column can be updated by:
oEntering a value in the yellow-background text box under the Update Apply Remark button and clicking the Update Apply Remark button (this will only update the Apply Remark in rows with the Apply Qualifier column populated), or
oEntering a new value in the Apply Remark column of an individual result.
Clicking the Filter button in any column header will open the filter selection window for that column.
•When the set_NQ_to_NULL Global Parameter = FALSE:
oIf DQM Q column = 'NQ', then Apply Q column = ‘NQ’;
oDuring the Process Event step, filter DQM Q column for ‘NQ’ and
omanually batch update the Apply Q column to NULL during the review process.
•When the set_NQ_to_NULL Global Parameter = TRUE:
oIf DQM Q column = 'NQ', then Apply Q column = NULL;
oThe results will still show up in the Exceptions tab of the DQM Process Event screen.
The Review Exceptions by Check tab shows the results which failed the selected checks during that Qualification run (Exceptions) and the results associated with those Exceptions (Associated Results). Analytical Result rows may be listed more than once if more than one check and/or rule was violated, but they are separated by check.
If there are exceptions, select the Review Exceptions by Check tab. For each DQM check with an exception, review and change the suggested qualifiers as needed. Elements of the Review Exceptions by Check tab are:
1.The different Exceptions are displayed by selecting a DQM Check in the drop-down box.
2.The Associated Results for that check are displayed in the lower section.
3.The visible results can be filtered and/or sorted by clicking the Filter button on any column header.
4.The Apply Qualifier can be updated by using the Include check boxes and the Update Q button.*
5.The Apply Remark can be updated by using the Update Apply Remark button.*
6.When an Exception record or an Associated Result record is selected, the associated data from the Analytical Results Report are displayed in the vertical list on the right.
*See General Notes above for more detail.
The Review All Results tab shows all of the results from the Analytical Results Report with the Apply Q, Apply Remark and DQM Q columns filled in for results with exceptions (if there are any). Each Analytical Result row is listed once. If there has been more than one check/rule violation, then the qualifiers are merged in this tab (merge function defaults to prevent duplicates in merged qualifier). See the Changing DQM Settings section on the Qualifiers tab for more information about populating the Rank column of the Qualifiers table to allow duplicates for selected qualifiers.
1.All records from the Analytical Results Report run during the Create DQM Event are displayed.
2.The visible results can be filtered and/or sorted by clicking the Filter button on any column header.
3.The Apply Qualifier can be updated by using the Include check boxes and the Update Q button.*
4.If a merged Apply Qualifier (from multiple Exceptions for the same result) is updated on this tab, the Apply Qualifier for all of the Exceptions is changed to the new value.
5.The Apply Remark can be updated by using the Update Apply Remark button.*
6.When a record is selected, all of the other columns in the Analytical Results Report are displayed in the vertical list.
*See General Notes above for more detail.
The DQM_STATUS and DQM_STATUS_REMARK columns in DT_SAMPLE allow samples with problems during the collection process to be marked. The AR II Report has a new input parameter that allows the user to select samples based on the value(s) in this column.
Elements of the Review Samples and Results by Sample tab are:
1.Each sample from the Analytical Results output is listed in the top section.
2.The Field Quality Comment data (if present and included in the DQM Event) are included for review.
3.The DQM_STATUS can be updated by:
4.Entering a value in the yellow-background text box under the Update DQM Status button and clicking the Update DQM Status button, or
5.Entering a new value directly in the DQM Status column of a sample row.
6.The DQM_STATUS_REMARK can be updated by:
7.Entering a value in the yellow-background text box under the Update DQM Status Remark button and clicking the Update DQM Status Remark button,or
8.Entering a new value directly in the DQM Status Remark column of a sample row.
9.The results for each selected sample are displayed in the lower section, and the Apply Qualifier and the Apply Remark can be modified as described in the Review All Results Tab section.
10.When a result record is selected, all associated data from the Analytical Results Report are displayed in the vertical list.
A DQM Event can be resaved during the review process and reopened to continue the review as described here.