add tabMWRwqx function

[fawda123]

Output for WQX will be an excel file with three tabs, use the writexl package on ROpenSci to do this, also include libxlsxwriter for Ubuntu build. The template WQX file includes a fourth logic tab that is not included in the output.

[fawda123]

Started here 31763927cffa710fb75f57c7c6bd85d57285ee8a

[fawda123]

Should be mostly done, but need to figure out how to correctly assign the Result Sample Fraction column to the results file because it creates a one to many join, i.e., there is no way to figure out how the WQX Parameter column in wqxdat maps to the results file.

[ben-wetherill]

The WQX Output is looking very nice and I think this will be a value add for groups that are struggling with WQX. Here are items I noticed:

1. In the locations tab, there is an extra row of Horizontal Collection Method and Horizontal Reference System values.
2. Sample Collection Method Context is not populating for parameters where the parameter name used in the WQX Meta file and/or Results file is the simple name.
3. Method Speciation, Result Sample Fraction, Result Analytical Method ID, and Result Analytical Method Context are not populating for parameters where the parameter name is the simple name. Same as number 2.
4. For Quality Control Samples, the BDL result values are showing in the Result Value column. They should be blank for BDL or AQL. Example: Total Phosphorus Lab Duplicate 2 6/13/21.
5. Result Detection Quantitation Limit Unit is not being populated at all. It should be populated when the result value is BDL or AQL.
6. Result Detection Quantitiation Limit Type and Measure are only being populated for QC entries. It should be populated for all results where the value is BDL or AQL. Example: TSS Sample ABT-026 8/15/21
7. Result Sample Fraction should be populated for Field Replicates and Field Blanks. This was not correctly documented in the WQX Output Design file. I have updated the document in version 11-16-22. Example: Ammonia Field Blank 7/11/21.
8. Result Unit should be blank if Result Value is BDL or AQL. Example: HBS-016 6/21/21 Chlorophyll a
9. Location ID should be populated for Field Blanks and Field Replicates. Example: CND-036, 7/11/21, Ammonia result 0.02.

[fawda123]

@ben-wetherill I think I've got these items taken care of now. Give it a go on your side when able. Thanks!

[ben-wetherill]

From the issue list above...

1. Fixed
2. Fixed
3. Fixed
4. Fixed
5. Fixed
6. Fixed
7. Fixed
8. Fixed
9. Fixed

New items (Sorry, I think I have been confusing myself with these activity types):

1. New logic: Sample Collection Method ID, Method Context, and Equipment Name need to be populated for Field Replicates and Field Blanks also. I updated the logic in the design file to "If input Activity Type = "Sample-Routine" or "Field Blank" (includes output "Field Replicate")..."
2. New logic: Result Sample Fraction, Analytical Method, and Analytical Context need to be populated for all QC activity types. I updated the logic in the design file to remove the "leave blank" logic. They should simply be populated based on the WQX meta.

WQX_Output_Design_11-23-22.xlsx

[ben-wetherill]

One more new logic item: For Sample Collection Method Context, add the logic "If Sample Collection Method ID was populated with the default of "Grab", use the default Method Context of "MassWateR"."

WQX_Output_Design_11-30-22.xlsx

[ben-wetherill]

Sorry. One more new logic item: Updated Activity ID formula to include abbreviations for Field Blanks and Lab Blanks.

WQX_Output_Design_11-31-22.xlsx

[fawda123]

@ben-wetherill no problem, the updates should now be included in tabMWRwqx.

For the Sample Collection Method ID, Method Context, and Equipment Name columns, the activity types for 'Sample-Routine', 'Quality Control Sample-Field Blank', 'Quality Control Sample-Field Replicate' are now included. I wasn't sure if 'Quality Control Field Replicate Msr/Obs' should also be included, so I left it out.

[ben-wetherill]

Status of new items:

1. Method ID, Context, Equipment name - looks good
2. Sample Fraction, Analytical Method, Method Context - looks good
3. Default Collection Method Context - looks good
4. Activity ID formula - looks good