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.
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.
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.
•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.