(v22) Maintain Derived Fields

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: The filter IS NULL does not work properly on grouped fields. The LOCATE text row-level function only supports a constant as a argument. A COALESCE conditional row-level function specified with and empty argument does not work properly. If the CASE conditional row-level function returns a null value as a an argument of another function, a NullPointerException may occur. The LPAD and RPAD text row-level functions are not supported.
Apache Phoenix Query Server (QS)	Y
Apache Solr	N
BigQuery	Y			If you need to access a BigQuery partition, explicitly include an alias for the built in partition column in your select clause, such as select *, _PARTITIONTIME as pt from projectId.datasetId.tableId.
Cloudera Impala	Y
Cloudera Search	N
Couchbase	Y
Dremio	N
Elasticsearch 7.0	Y
Elasticsearch 8.0	Y
File Upload	Y
HDFS	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.
MySQL	Y
Oracle	Y
PostgreSQL	Y
Real Time Sales	N/A
SAP Hana	Y
SAP IQ	Y
Snowflake	Y
Spark SQL	Y
Teradata	Y
TIBCO DV	Y
Trino	N
File Upload (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:

(revenue/lengthofemployment)

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:

• Derived Field Editor
• Create and Modify Derived Fields
• Duplicate Derived Fields
• Supported Row-Level Functions
• Delete Derived Fields
• Hide Fields