The speed with which consultants are able to analyse and plot laboratory data is the main reason why AGS data is being requested more frequently in laboratories working on medium to large construction projects. For a significant number of laboratories the production of AGS data is causing a number of problems but these can be easily avoided if the requirements are clearly thought out at the start of a project.
The data contained within an AGS file generated by a laboratory can be split into two broad categories; sample data and test data.
The sample data is generally passed to the laboratory from the customer and consists of location data and sample parameters. This data is traditionally supplied to the laboratory either via an Excel schedule sheet or simply via a paper hard copy. How to best handle this type of data was covered in Part I of this article.
The test data are the results that the laboratory has produced from the tests carried out. What is included in this data and how to create it is covered in this article.
The data contained within an AGS file is classified as engineering data and so a laboratory is only required to include the results and any supporting data required by the appropriate testing standard. Raw lab data, such as tin weights, or consolidation curves are not required. A full list of data for each tests is included in the AGS Data transfer publication that can be downloaded from www.ags.org.uk
Each test type has one or multiple groups in an AGS file. Simple tests, such as Moisture content or Density tests have a single group that records the results and relevant testing parameters. Multistage testing such as Triaxial or Consolidation tests have two groups, often referred to as a paired table. The first group contains the results for the tests and the second table contains results for each stage within the test.
Where multiple testing has been carried out on a sample then the AGS data requires a specimen reference number and specimen depth to ensure that the data is correctly identified to the location on the sample and so every laboratory testing table in AGS has SPEC_REF and SPEC_DPTH headings.
Creating AGS data
The easiest way to create Laboratory AGS data is to use an AGS compatible Laboratory Information Management System (LIMS), however, at time of writing, KeyLAB from Keynetix is the only LIMS that is listed on the AGS website as AGS compatible, so there is a strong chance that if you are reading this article you do not yet have the option to simply select AGS export from your existing LIMS systems.
Excel to the rescue
There is a cheap application available called KeyAGS that allows you to create AGS data from spreadsheets and this can be used for laboratories that use Excel spreadsheets to report their data or can export data from their LIMS system into spreadsheet format. However how you prepare your worksheets is important.
Customers who choose this route should create an additional tab on their spreadsheets and set up this worksheet to pull data from the laboratory worksheet and put in a simple AGS style grid on the spreadsheet using simple Excel equations. This then allows changes to be made to the report sheet or worksheet without affecting the AGS output sheets
The example below shows this arrangement for a sieve sheet. No data entry is needed on the “Data – AGS” sheet as all the information required by this sheet is pulled from the “sheet1” sheet using equations. Laboratories that adopt this method will often hide the “Data – AGS” sheet so that the users of the workbook see no difference between their old sheet and one that has been enabled for AGS export.
The KeyAGS application allows you to export and merge data from more than one workbook at once so creating AGS data can be completed by simply opening all your test workbooks and selecting the export all option. This process takes care of all the additional reference tables required by AGS as well as the test data. An example file created from the above workbook is shown below.
Early adoption = Significant cost saving
If you have data entered into a worksheet that has not been set up for KeyAGS then it is easy to add the sheet and equations as shown below, however if you have a large number of these sheets then this process becomes too time-consuming to be practical. In these cases it is common to create a script file that adds the sheet and mapping to each sheet one at a time and automates the production of AGS data but this is something that is outside of this article’s scope.
Creating AGS data from laboratory test data does not need to be difficult or expensive to do. If the volume of AGS data produced by the laboratory is small then Excel spreadsheets can be used to create the data but careful preparation of the templates is important before the data is added to any sheets.
Dr Roger Chandler is the Managing Director of Keynetix and has served on the AGS data management committee for 15 years. Keynetix produce a wide range of AGS compatible software such as KeyLAB, KeyAGS and HoleBASE SI. For more information please visit www.keynetix.com