Functions

Special Functions

Functions that require explanation in greater detail are listed in this section.

LookupWithUnit

LookupWithUnit("<<Lookup Table.Field>>","SQL Where Clause"[,Is Library Lookup true/false][, Required Unit])

The LookupWithUnit function returns the first value from the specified field of the Lookup table, where the record satisfies the conditions of the SQL Where Clause, and if a project table, is a corresponding keyset record.

Below is a definition of the parameters for LookupWithUnit:

• "<<Lookup Table.Field>>" – Table field reference of the value to look up. This can be from either Project or Library.

"SQL Where Clause" – SQL compatible Where Clause to filter the lookup results. Table/Field references must be in the <<Table.Field>> format, and must be from the same table as the Table in <<Lookup Table.Field>>.

Variables defined in the Variables grid can be referenced here. For example, if a variable called qc has been declared as a Variable in the Variables grid, there Where Clause can reference qc, and it will be replaced with the value during the calculation.

When using variable references with operators, there must be a space between the variable and the operator. For example, (Qc = 1) is ok, (Qc=1) is not.

• Is Library Lookup true/false (optional) – Determines whether to query the library only, or the project only, or both library and project. Entering true will force the function to query the library only, false will force the function to query the project only. If omitted, the function will try to query the library first, if table/field does not exist then it will query the project.
• Required Unit (optional) – Unit the returned value must be in. If the Required Unit does not match the unit of the <<Lookup Table.Field>>, then it will be converted to the Required Unit.

The LookupWithUnit function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

Below is an example usage of the LookupWithUnit function.

LookupWithUnit(
"<<DG_CPT_PILE_AXIAL_CAPACITY_LCPC_SOIL.Soil_Description>>"
,
"(<<DG_CPT_PILE_AXIAL_CAPACITY_LCPC_SOIL.Soil_Type>> = SoilType) and
(qc > <<DG_CPT_PILE_AXIAL_CAPACITY_LCPC_SOIL.Cone_Resistance_Minimum>>) and
(qc <= <<DG_CPT_PILE_AXIAL_CAPACITY_LCPC_SOIL.Cone_Resistance_Maximum>>)"
,
true
)

LookupIntervalRangeWithUnit

LookupIntervalRangeWithUnits("<<Current Interval Table.Field>>","<<Lookup Value Table.Field>>","<<Top Interval Table.Field>>","<<Bottom Interval Table.Field>>","Return Units")

The Lookup Interval Range With Unit function returns the value for a specified field from another depth related table, where the current interval value in the CPT_DATA table lies in between the Top and Bottom intervals in the other table.

Below is a definition of the parameters for LookupIntervalRangeWithUnits:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value that is used as a search criteria for the value on the other table. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.

• "<<Lookup Value Table.Field>>" – Other table field reference for the value to retrieve.
• "<<Top Interval Table.Field>>" – Other table field reference to the field that contains the top interval values
• "<<Bottom Interval Table.Field>>" – Other table field reference to the field that contains the bottom interval values
• "<<Return Unit>>" – Unit the returned value must be in. If the Return Unit does not match the unit of the <<Lookup Table.Field>>, then it will be converted to the Return Unit.

The Current Interval must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table. The Top and Bottom interval and Lookup Value table field references must refer to fields from the same table.

The LookupIntervalRangeWithUnit function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

The LookupIntervalRangeWithUnit function assumes that there is only one set of top and bottom intervals for each CPT data keyset, and that the top and bottom interval values do not create overlapping layers. If more than one matching value for the current interval is found, then the first value is returned.

The LookupIntervalRangeWithUnit function assumes <<Current Interval Table.Field>>, <<Top Interval Table.Field>> and <<Bottom Interval Table.Field>> to be in the same units (e.g. m).

Below is an example usage of the Lookup Interval Range With Unit function that retrieves the Bulk Unit Weight value in kN/m³ from the Bulk_Unit_Weight field in the CPT_MATERIAL_PROPERTIES table, where the current CPT_DATA depth lies between the top and bottom depths in the CPT_MATERIAL_PROPERTIES table.

LookupIntervalRangeWithUnit(
"<<CPT_DATA.Depth>>"
,
"<<CPT_POINT_MATERIAL_PROPERTIES.Bulk_Unit_Weight>>"
,
"<<CPT_POINT_MATERIAL_PROPERTIES.Depth>>"
,
"<<CPT_POINT_MATERIAL_PROPERTIES.Bottom>>"
,
"kN/m3"
)

FixedIntervalAverage

FixedIntervalAverage("<<Current Interval Table.Field>>","<<Values To Average Table.Field>>",Top Boundary, Bottom Boundary, "Interval Units", "Return Units")

The Fixed Interval Average function returns an arithmetic mean of values where the current interval value lies between the Top and Bottom boundaries.

Below is a definition of the parameters for FixedIntervalAverage:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value that is used as a search criteria for the values to average. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.

The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.

• "<<Values to Average Table.Field>>" – The table field reference for the values to average.
• Top Boundary –The top interval values
• Bottom Boundary –The bottom interval values
• "Interval Units" – The units for interval value
• "Return Units" – The units for returned value

The Current Interval must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table. The Values to Average table field references must refer to fields from the same table.

The FixedIntervalAverage function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

Below is an example usage of the Fixed Interval Average function that calculates the arithmetic mean of the Effective Stress value (in kPa) from the Effective_Stress field in the CPT_DATA_FOUNDATION table, where the current CPT_DATA depth lies between the top (=1 m) and bottom (= 2 m) depths.

FixedIntervalAverage(
"<<CPT_DATA.Depth>>"
,
"<<CPT_DATA_FOUNDATION.Effective_Stress>>"
,
1
,
2
,
"m"
,
"kPa"
)

MaxMinValueOutOfRange

MaxMinValueOutOfRange("<<Value Table.Field>>",Boundary Value ,Value Less Than Boundary)

The Maximum Minimum Value out of Range function returns the maximum or minimum value out of a range of values that exceeds a boundary value.

For example, if the *<<Value Table.Field>>* is <<CPT_DATA.Cone_Resistance>>, the *Boundary Value* is 27 and *Less Than Boundary* set to False then wherever a range of Cone Resistance values exceed 27, it will return the highest Cone Resistance within that range, at the row where the highest value occurs.

Below is a definition of the parameters for MaxMinValueOutOfRange:

• "<<Value Table.Field>>" – Table field reference to the value to retrieve the range of values exceeding the boundary, and the maximum or minimum value within that range
• Boundary Value – Boundary value, must be numeric
• Value Less Than Boundary – Value Less Than Boundary is a boolean field, assign true to retrieve minimum, false or leave blank to get maximum. This parameter is optional. If the parameter is empty or not defined, False (maximum) is assumed.

MaxMinValueOutOfRange("<<CPT_DATA.Cone_Resistance>>",27,True)

The Maximum Minimum Value out of Range function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

MovingAverage

MovingAverage(
  "<<Current Interval Table.Field>>",
  "<<Values to Average Table.Field>>",Thickness[,Stop Calculation on Gaps]
)

The Moving Average function returns an arithmetic mean of values above and below the current interval field value. Below is a definition of the parameters for MovingAverage:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value, and the range of averages. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.

• "<<Values to Average Table.Field>>" – Table field reference for the values to average. The data set of the average is obtained from this field. The field does not need to be a numeric type, but the values must be numeric. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
• Thickness – Defines the interval range of the values taken for the average, where the current interval is the middle of the range. Therefore, the minimum and maximum range of the average would be the current interval + thickness / 2 and current interval - thickness / 2 respectively.
• Stop Calculation on Gaps – Stops retrieving values above or below the current interval field value if an empty value is found. Assign true to stop calculation on gaps, false otherwise. This parameter is optional. If the parameter is empty or not defined, false (do not stop calculation on gaps) is assumed.

The Current Interval and the Values to Average table field references must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table.

If the MovingAverage function is used with a correlation evaluated by the Formula Tool, then the Iteration of the moving average formula must be greater than the iteration of the correlation.

The MovingAverage function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

The MovingAverage function assumes the thickness is in the same units as <<Current Interval Table.Field>>.

Below is a working example configuration of $//<![CDATA[ \begin{array}{l}q_c\end{array} //]]>$ Moving Average:

In this example, an average of qc values above and below the given depth will be written to the Cone_Resistance_Moving_Average field in the CPT_DATA field. The distance above and below can be configured in the CPT_PROJECT_PARAMETERS table, in the field Moving_Average_Thickness. The thickness defines the depth range of the $//<![CDATA[ \begin{array}{l}q_c\end{array} //]]>$ values taken, where the given depth is in the middle of the range.

For example, specifying a Moving_Average_Thickness of 1 m, the $//<![CDATA[ \begin{array}{l}q_c\end{array} //]]>$ values are taken from ±0.5 m from the given depth.

MovingAverageConstrainedLCPC

MovingAverageConstrainedLCPC(
"<<Current Interval Table.Field>>", 
Current Interval Required Unit, 
"<<Values to Average Table.Field>>", 
Values to Average Required Unit, 
DistanceLesserDepth, 
DistanceGreaterDepth, 
ConstrainingMinimumFactor,
ConstrainingMaximumfactor)

The Moving Average Constrained LCPC function calculates a type of moving average known as the LCPC method as specified in the paper written by Bustamante and Gianeselli (1982). This function used to calculating the Pile Unit End Bearing .

Below is a definition of the parameters for MovingAverage:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value, and the range of averages. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.
• Current Interval Required Unit – Unit the current interval must be in. If the current interval value is not in the specified unit, it will be converted to be in that unit.
• "<<Values to Average Table.Field>>" – Table field reference for the values to average. The data set of the average is obtained from this field. The field does not need to be a numeric type, but the values must be numeric. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
• Values to Average Required Unit – Unit the values to average must be in. If the Values to Average value not in the specified unit, it will be converted to be in that unit.
• Distance Lesser Depth – Furthermost distance above the current interval i.e. over the cone. This represents +a in the LCPC method definition.
• Distance Greater Depth – Furthermost distance below the current interval i.e. below the pile point. This represents -a in the LCPC method definition.
• Constraining Minimum Factor – Factor multiplied to $//<![CDATA[ \begin{array}{l}q_{ca}'\end{array} //]]>$ to determine the minimum peak clipping boundary. Value given in the LCPC method definition is 0.7.
• Constraining Maximum Factor – Factor multiplied to $//<![CDATA[ \begin{array}{l}q_{ca}'\end{array} //]]>$ to determine the maximum peak clipping boundary. Value given in the LCPC method definition is 1.3.

The Current Interval and the Values to Average table field references must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table.

If the MovingAverageConstrainedLCPC function is used with a correlation evaluated by the Formula Tool, then the Iteration of the moving average formula must be greater than the iteration of the correlation.

The MovingAverageConstrainedLCPC function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

Below is a working example configuration of $//<![CDATA[ \begin{array}{l}q_p\end{array} //]]>$ Pile Unit End Bearing:

NextValue

NextValue("<<Next Value Table.Field>>" ,Required Unit)

Next Value function gets the value from the next row from the current calculation row for the specified field. NextValue only works for the current calculation table CPT_DATA, or a one to one child of CPT_DATA.

Below is a definition of the parameters for NextValue:

• "<<Next Value Table.Field>>" – Table field reference to retrieve the next value from. As explained above, the Field must be one from CPT_DATA, or a one to one child of CPT_DATA.
• Required Unit (optional) – Unit the returned value must be in. If the Required Unit does not match the unit of the <<Next Value Table.Field>>, then it will be converted to the Required Unit.

Below is an example usage of the NextValue Function:

NextValue("<<CPT_DATA_FOUNDATION.Pile_Axial_Capacity_Side_Friction_1>>","MN")

PreviousValue

PreviousValue("<<Previous Value Table.Field>>" [,Required Unit])

The Previous Value function gets the value from the row previous to the current calculation row for the specified field. PreviousValue only works for the current calculation table  CPT_DATA, or a one to one child of CPT_DATA.

Below is a definition of the parameters for PreviousValue:

• "<<Previous Value Table.Field>>" – Table field reference to retrieve the previous value from. As explained above, the Field must be one from CPT_DATA, or a one to one child of CPT_DATA.
• Required Unit (optional) – Unit the returned value must be in. If the Required Unit does not match the unit of the <<Previous Value Table.Field>>, then it will be converted to the Required Unit.

Below is an example usage of the PreviousValue Function:

PreviousValue("<<CPT_DATA_FOUNDATION.Pile_Axial_Capacity_Side_Friction_1>>","MN")

SteppedAverage

SteppedAverage("<<Current Interval Table.Field>>","<<Values to Average Table.Field>>",Thickness)

The Stepped Average function returns an arithmetic mean of values within an interval range that increments by the specified thickness. A graph of a set of stepped average values against the interval will appear as horizontal steps of equal width that step up or down.

Below is a definition of the parameters for SteppedAverage:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value, and the range of averages. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
• The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be depth.
• "<<Values to Average Table.Field>>" – Table field reference for the values to average. The data set of the average is obtained from this field. The field does not need to be a numeric type, but the values must be numeric. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
• Thickness – Defines the interval increment range of the values taken for the average.

The Current Interval and the Values to Average table field references must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table.

The SteppedAverage function assumes the thickness is in the same units as <<Current Interval Table.Field>>.

The SteppedAverage function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

If the SteppedAverage function is used with a correlation evaluated by the Formula Tool, then the Iteration of the moving average formula must be greater than the iteration of the correlation.

Below is a working example configuration of SPT N60 Stepped Average:

The SPT average N60 value is a stepped average of values in the SPT_N60_1 field on the CPT_DATA table for each defined depth interval step.

The average of the N60 values is calculated for each depth interval step, and the result is recorded in the SPT_Average_N60_1 field in the CPT_DATA table for each row in that interval step.

The depth interval distance (thickness) is defined on the SPT_N60_Average_Interval field on CPT_POINT_PARAMETERS and CPT_PROJECT_PARAMETERS tables. The first function dictates that the value in the CPT_POINT_PARAMETERS table has precedence over the CPT_PROJECT_PARAMETERS table.

For example, specifying a SPT_N60_Average_Interval of 1 m, the stepped average value at a depth of 0.5 m would be the average SPT N60 values between 0 m and 1 m.

SteppedExtrapolation

SteppedExtrapolation("<<Current Interval Table.Field>>","<<Values to Extrapolate Table.Field>>")

The Stepped Extrapolation function returns a value that is extrapolated to the midpoint between the current value to extrapolate, and the next value to extrapolate. From the current value to extrapolate to the midpoint, Stepped Extrapolation returns the current value to extrapolate, and from the midpoint to the next value to extrapolate, Stepped Extrapolation returns the next value to extrapolate.

Below is a definition of the parameters for SteppedExtrapolation:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value, and is a reference point to determine the midpoint between two values to extrapolate. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.

• "<<Values to Extrapolate Table.Field>>" – Table field reference for the values to extrapolate. The field does not need to be a numeric type, but the values must be numeric. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.

The Current Interval and the Values to Extrapolate table field references must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table.

The Stepped Extrapolation cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

Below is a working example configuration Shear Wave Velocity Extrapolated:

Results:

TableIntervalAverage

TableIntervalAverage("<<Current Interval Table.Field>>","<<Values to Average Table.Field>>","<<Top Interval Table.Field>>","<<Bottom Interval Table.Field>>")

The Table Interval Average function returns an arithmetic mean of values within an interval range specified on another Table. A graph of a set of table interval average values against the interval will appear as horizontal steps whose width is top and bottom intervals specified on the corresponding table. Conceptually, the Table Interval Average is similar to the Stepped Average, but the step widths are defined by the Top and Bottom interval values.

Below is a definition of the parameters for TableIntervalAverage:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value, and the range of averages. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.

• "<<Values to Average Table.Field>>" – Table field reference for the values to average. The data set of the average is obtained from this field. The field does not need to be a numeric type, but the values must be numeric. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
• "<<Top Interval Table.Field>>" – Table field reference to the field that contains the top interval values
• "<<Bottom Interval Table.Field>>" – Table field reference to the field that contains the bottom interval values

The Current Interval and the Values to Average table field references must refer to a field from the CPT_DATA table, or a one to one child of the CPT_DATA table. The Top and Bottom interval table field references must refer to fields from the same table.

The TableIntervalAverage function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.

The TableIntervalAverage function assumes that there is only one set of top and bottom intervals for each CPT data keyset, and that the top and bottom interval values do not create overlapping layers.

The TableIntervalAverage function assumes <<Current Interval Table.Field>>, <<Top Interval Table.Field>> and <<Bottom Interval Table.Field>> to be in the same units (e.g. m).

Below is a working example configuration of $//<![CDATA[ \begin{array}{l}q_c\end{array} //]]>$ Strata Average:

In this example, the cone resistance is averaged between the strata layers as defined by the Depth and Bottom fields in the STRATA_MAIN table, for the current PointID.

ValueOutOfRangeAtInterval

ValueOutOfRangeAtInterval(
"<<Current Interval Table.Field>>",
"<<Source Table.Field>>",
Boundary Value,
Interval Value
[,Value Less Than Boundary])

The Value out of Range at Interval function returns values in the Source field that exceed a boundary value at specified intervals. If the value exceeds the boundary value but does not occur on the interval value, then no value is returned.

Consider this example:

• <<Current Interval Table.Field>> is <<CPT_DATA.Depth>>
• <<Source Table.Field>> is <<CPT_DATA.Cone_Resistance>>
• Boundary Value is 27
• Interval Value is 0.25
• Less Than Boundary set to False

At every 0.25 depth intervals (e.g. 1, 1.25, 1.50), the function will check to see if Cone Resistance at that depth interval is greater than the Boundary Value. If Cone Resistance at a depth of 2.75 is 30, then the function will return 30.
If the Cone Resistance is not greater than the Boundary Value, or it is greater than the boundary value but is not at the specified depth interval, then no value is returned.
Below is a definition of the parameters for ValueOutOfRangeAtInterval:

• "<<Current Interval Table.Field>>" – Table field reference to the interval that determines the current interval value. As denoted by the parameter name, the table field reference must be surrounded with quotation marks.
  The values in the interval field must be uniformly increasing or decreasing in value with no duplicates. A typical interval field would be Depth.
• "<<Value Table.Field>>" – Table field reference to the value to check if they exceed the boundary
• Boundary Value – Boundary value, must be numeric
• Value Less Than Boundary – Value Less Than Boundary is a boolean field, assign true to return the value if it is less than the Boundary Value, false to return the value if it is greater than the Boundary Value. This parameter is optional. If the parameter is empty or not defined, False (greater than Boundary Value) is assumed.

The Value out of Range at Interval function cannot be evaluated via the Validate button or in Validation Reports from the Formula Tool Configuration Form.