Maintain Derived Fields

Derived fields are supported by certain connectors that come out-of-the-box in Composer. To see what functions are available, see Supported Row-Level Functions.

Support for this feature by Composer connectors is shown in the following table.

Key: Y - Supported; N - Not Supported; N/A - not applicable

Connector Supported? Notes
Amazon Redshift Y  
Amazon S3 Y
Apache Drill Y  
Apache Phoenix Y

Apache Phoenix and Apache Phoenix Query Server connectors support row-level expressions (derived fields) with the following limitations:

Apache Phoenix Query Server (QS) Y
Apache Solr N
BigQuery Y  
Cloudera Impala Y
Cloudera Search N
Couchbase Y
Dremio N  
Elasticsearch 6.0 Y
Elasticsearch 7.0 Y
Flat File Y  
Hive Y  
MemSQL Y  
Microsoft SQL Server Y  
MongoDB Y

MongoDB connectors support derived fields with some exceptions. See the discussion in Manage the MongoDB Connector.

Oracle Y
PostgreSQL Y  
Presto N  
Real Time Sales N/A  
SAP Hana Y  
Snowflake Y  
Spark SQL Y
Teradata Y  
Upload API Y  
Vertica Y  

A derived field is an in-memory column for your data table that is populated with results from calculations performed on data already in your table. You can create derived fields using row-level expressions that are built using row-level functions.

These calculations are performed at the level of a row, that is, a record, and do not include other data from your table that is outside of that particular row. If a source supports derived fields, then you can use them as arguments for aggregate functions when creating other calculations.

Derived fields can be created from other derived fields.

Consider the following examples:

Your data source has records that list the revenue generated and the term of employment but does not have an average of the two. You can use a derived field to create an average of the two for each record. Use the following formula:


Your data source continues values that have been brought in as text strings. In order to cross-reference this data with the time values, you need to change the text to a numeric value. Use the following formula as a base:

TEXT_TO_NUM (LTRIM (Field_A, '$')) SUM(TEXT_TO_NUM(SUBSTRING("$124456.00", 2, 10)))

Your data source contains records that list the start of employment and termination of employment for your company. You want to find the differences between these time values to average out the length of employment. Use the following formula as a base:

TIME_DIFF (timePart, startTime : Time, endTime : Time) : Numeric

Composer supports row-level functions in derived fields. See Supported Row-Level Functions.

Derived fields can be hidden. See Hide Fields.

For information on maintaining derived fields, see the following links: