Using calculated fields in schema

The formula field property can be used to add calculated fields to your connector's schema.

The full list of functions that are supported in formulas can be found at Function List. Additionally, there is further documentation for:

When referring to another field's name in a formula, proceed it with a $. For example, if your field name is t_celsius, it should be referred to as $t_celsius in the formula.

semanticType for calculated fields

To set the semanticType for a formula:

  • If the output is a number, set semanticType to NUMBER or PERCENT.
  • If the output is a string and matches a DATE_AND_TIME or GEO semanticType, set the semanticType to the corresponding enum. For example, if the output for the calculated field is "20180327", set semanticType to YEAR_MONTH_DAY.
  • For all other string outputs, set the semanticType to TEXT.

defaultAggregationType for calculated fields

To set the defaultAggregationType for a formula:

  • If your formula involves an aggregation such as SUM, AVERAGE, or MAX, the defaultAggregationType should be set to AUTO. For example, the formula: SUM($cost) / SUM($units) should have a defaultAggregationType of AUTO. Check the type column in the Function List to see if a function returns an Aggregation.
  • If your formula does not involve an aggregation, set the defaultAggregationType to any option except AUTO.

Examples of calculated fields in schema

The following schema contains two calculated fields, t_fahrenheit and weather. Note that their formula properties prepend a $ to names they refer to.

{
  "schema": [
    {
      "name": "date",
      "label": "Date",
      "description": "Date of measurement",
      "dataType": "STRING",
      "semantics": {
        "conceptType": "DIMENSION",
        "semanticType": "YEAR_MONTH_DAY",
      }
    },
    {
      "name": "t_celsius",
      "label": "Temperature (Celsius)",
      "description": "Temperature in Celsius",
      "dataType": "NUMBER",
      "semantics": {
        "conceptType": "METRIC",
        "semanticType": "NUMBER",
      }
    },
    {
      "name": "t_fahrenheit",
      "label": "Temperature (Fahrenheit)",
      "description": "Temperature in Fahrenheit",
      "dataType": "NUMBER",
      "formula": "$t_celsius/5*9+32",
      "semantics": {
        "conceptType": "METRIC",
        "semanticType": "NUMBER",
      }
    },
    {
      "name": "weather",
      "label": "Weather (based on temp)",
      "description": "Temperature in Fahrenheit",
      "dataType": "STRING",
      "formula": "CASE WHEN $t_celsius<0 THEN \"Freezing\" WHEN $t_celsius>40 THEN \"Too warm\" ELSE \"Not bad\" END",
      "semantics": {
        "conceptType": "DIMENSION",
        "semanticType": "TEXT"
      }
    }
  ]
}