Export AQS Data Using EQuIS AQS Format

<< Click to Display Table of Contents >>

Export AQS Data Using EQuIS AQS Format

The AQS-export.xme file within the EQuIS AQS Format is used to generate pipe-delimited text files for upload to the EPA Air Quality System for each AQS Transaction Type. The AQS.zip format file is available for download from the Products\AQS Module folder on the Downloads Dashboard on the EarthSoft Community Center (ECC).

 

The sections in the AQS Format mimic the EPA AQS Transaction Formats. A complete list as well as descriptions of the AQS Transaction Types are available at https://aqs.epa.gov/aqsweb/documents/TransactionFormats.html.

 

EQuIS AQS Format EDD sections are available for the following AQS Transaction Types.

AA – Site Basic

AB – Site Street

MB – Monitor Basic (coming soon)

RD – Raw Data

RB – Raw Blanks

QA – 1-Point QC

QA – Annual PE

QA – Flow Rate Verification

QA – Semi Annual Flow Rate Audit

QA – Duplicate (coming soon)

QA – Replicate (coming soon)

QA – Pb Analysis Audit (coming soon)

QA – Zero Span

 

If the desired AQS transaction type is not in the above list, please contact Support@EarthSoft.com.

 

To view the EQuIS database mappings for each EDD section/field, an EDD Description file for the AQS Format can be generated from EDP.

 

Export AQS Data

 

To export AQS data, open the AQS Format file in EQuIS Professional EDP. Select the Professional ribbon and click the EDD drop-down menu in the Export section of the ribbon.

AQS-EDD_Export2

 

In the dialog window that opens, select the appropriate filter criteria from the available options:

Location (individuals and groups)

Analytic method

Analyte (individuals and groups)

Transaction type

Date range

Assessment Type (for QA Transactions)

 

When ready, click the green arrow button on the mini-toolbar to run the export.

AQS-EDD_Export-Window-Raw

 

Note: To export the results for all transaction types, dates, analytes, locations, and methods from the facility, do not select any criteria and simply click the green arrow button to run the export.

 

 

Important Considerations

 

For data exported using the EQuIS AQS Format:

The State, County, Site ID field will be populated using the DT_SAMPLE.SYS_LOC_CODE field, which needs to be a concatenation of the same State-County-Site ID fields (e.g., 27-003-1002) in that order.

The RAW_DATA and RAW.BLANKS.ACTION_INDICATOR fields are currently not mapped to the EQuIS database. It is defaulted to “I” in the Export.

For the Raw Transactions, the POC value will be pulled from the DT_SAMPLE_PARAMETER.PARAM_VALUE field with the PARAM_CODE = "AQS_POC".

For the QA Transactions, the POC value will be pulled from the DT_EQUIPMENT_PARAMETER.

If populated, the incoming SCREENING_GROUP_CODE will be stored in DT_SAMPLE_PARAMETER.PARAM_VALUE with the PARAM_CODE = "AQS_Screening_Group".

The RAW_DATA/RAW_BLANKS.FRACTION field defaults to "Total". If "Total" is not a valid reference value in the EQuIS database, EarthSoft recommends that a remap be setup for this field to translate "Total" into an appropriate internal reference value from the RT_FRACTION table (e.g., “T”).

The AQS Format will look for specific values when exporting data. Review the format’s EDD Description document and the Collect Template mappings for the exact values used for each Transaction and field.

Example: To populate QA_Flow_Rate_Verification.Monitor_Flow_Rate, the format will look in DT_EQUIPMENT_PARAMETER for PARAM_CODE = Monitor_Flow.

Example: To populate QA_Semi_Annual_Flow_Rate_Audit.Monitor_Flow_Rate, the format will look in DT_EQUIPMENT_PARAMETER for PARAM_CODE = Monitor_Flow_SA

To populate the DT_SAMPLE.SAMPLE_TYPE_CODE, the format will look for a valid remap in RT_REMAP_DETAIL with the REMAP_CODE = “AQS”% and EXTERNAL_FIELD = “Transaction_Type”.

For the Raw sections, the AQS Qualifier Codes will be exported from the DT_RESULT.AQS_QUALIFIERS field. The NULL_DATA_CODE field will be in the first position with the QUALIFIER_CODE_* fields following in numeric order.

For the RAW_BLANKS export, the BLANK_TYPE field will be populated using the DT_SAMPLE.SAMPLE_TYPE_CODE and the RT_REMAP_DETAIL records specified below:

REMAP_CODE=AQS_%

EXTERNAL_FIELD = BLANK_TYPE

INTERNAL_VALUE = <DT_SAMPLE.SAMPLE_TYPE_CODE>

EXTERNAL_VALUE = <RAW_BLANKS.BLANK_TYPE>

Example: A user wants the records with DT_SAMPLE.SAMPLE_TYPE_CODE = FB to use BLANK_TYPE = FIELD.

EXTERNAL_FIELD = BLANK_TYPE

INTERNAL_VALUE = FB

EXTERNAL_VALUE = FIELD

When exporting EQuIS data into EPA AQS file formats using the EQuIS AQS Format, the sample/test/result records will be separated into EDD Format sections using the DT_SAMPLE.SAMPLE_TYPE_CODE value. The RT_REMAP_DETAIL table needs to contain the following record(s):

RT_REMAP_DETAIL.EXTERNAL_FIELD = SAMPLE_TYPE_CODE

RT_REMAP_DETAIL.REMAP_CODE = AQS_%

RT_REMAP_DETAIL.EXTERNAL_VALUE = <a single EDD section name> (e.g., RAW_DATA, RAW_BLANKS, QA_REPLICATE, QA_DUPLICATE)

RT_REMAP_DETAIL.INTERNAL_VALUE = <DT_SAMPLE.SAMPLE_TYPE_CODE>

Example 1: A user wants records with SAMPLE_TYPE_CODE = “N” to load to the RAW_DATA section.

RT_REMAP_DETAIL.EXTERNAL_FIELD = SAMPLE_TYPE_CODE

RT_REMAP_DETAIL.REMAP_CODE = AQS

RT_REMAP_DETAIL.EXTERNAL_VALUE = RAW_DATA

RT_REMAP_DETAIL.INTERNAL_VALUE = N

Example 2: A user wants records with SAMPLE_TYPE_CODE = “QC-FR” to load to the QA_REPLICATE section.

RT_REMAP_DETAIL.EXTERNAL_FIELD = SAMPLE_TYPE_CODE

RT_REMAP_DETAIL.REMAP_CODE = AQS

RT_REMAP_DETAIL.EXTERNAL_VALUE = QA_REPLICATE

RT_REMAP_DETAIL.INTERNAL_VALUE = QC-FR

Any result data will be compared against the FEDERAL_MDL, MIN_VALUE, MAX_VALUE, and DIGITS fields in the RT_AQS_METHOD table. Warning messages will be thrown if data does not comply with these rules; warnings can be ignored in EDP.

Results should be less than the FEDERAL_MDL.

Results should be between the MIN_VALUE and MAX_VALUE.

Results should have the minimum number of decimal places specified by the DIGITS value.