# Transformation Types

Source: https://userguide.qualytics.io/fields/computed-fields/deep-dive/transformation-types/

Every Computed Field is built around a transformation type. Four are available today: **Cast**, **Cleaned Entity Name**, **Convert Formatted Numeric**, and **Custom Expression**. Pick the type that matches the shape of the value you want to derive.

## Cast

The **Cast** transformation converts field values to a specified target data type using Spark SQL casting rules. Use it when a field is ingested as a string but needs to behave as a numeric, date, timestamp, or boolean value for profiling, checks, or downstream computations.

### When to Use Cast

Reach for **Cast** when:

- A numeric field is ingested as a string (for example, `"1000"` instead of `1000`).
- Dates or timestamps need explicit typing.
- Boolean logic is required on string values.
- Quality checks or aggregations require strict data types.

### Options for Cast

| Field | Description |
| --- | --- |
| **Name** | A clear, descriptive name for the new computed field you are creating. |
| **Transformation Type** | Choose **Cast** to convert the data type of an existing field. |
| **Field** | The existing column whose values you want to convert into another data type. |
| **Target Type** | The data type you want the field to become, written as a SQL type name (for example: `int`, `bigint`, `double`, `decimal(10,2)`, `date`, `timestamp`, or `boolean`). Generic names such as `number`, and `decimal` without a precision and scale, are rejected. |
| **Format** | Optional. Used only when converting text into dates or timestamps, so the system knows how to read the date format correctly. |
| **Additional Metadata** | Optional information you can attach to the field to provide extra context or documentation. |

### Target Type Examples

| Input Value | Target Type | Result |
| --- | --- | --- |
| `"1000"` | `int` | `1000` |
| `"1234.56"` | `decimal(10,2)` | `1234.56` |
| `"true"` | `boolean` | `true` |
| `"2023-12-31"` | `date` | `2023-12-31` |

### Format Examples (Date / Timestamp Casting)

The **Format** field is optional and applies only when casting text into `date` or `timestamp`. Leave it empty when the text already uses a standard layout such as `2023-12-31` or `2023-12-31 14:30:00`, and fill it in when the text uses a different one.

Common examples:

| Format Pattern | Example Input |
| --- | --- |
| `MM/dd/yyyy` | `12/31/2023` |
| `dd/MM/yyyy` | `31/12/2023` |
| `yyyy-MM-dd HH:mm:ss` | `2023-12-31 14:30:00` |

## Cleaned Entity Name

The **Cleaned Entity Name** transformation removes common business signifiers from entity names, producing a cleaner and more uniform version of the source value.

### Options for Cleaned Entity Name

| Field | Description |
| --- | --- |
| **Name** | Defines a unique name for the computed field. This name is used to identify and reference the computed field. |
| **Transformation Type** | Select **Cleaned Entity Name** to create a standardized and cleaned version of the selected source field value. |
| **Field** | Specifies the source field whose values will be cleaned and transformed. |
| **Term Settings** | On/off switches that choose **where** terms are removed. The terms come from a built-in list of business signifiers (such as `Inc.`, `Ltd`, `LLC`, `Co.`), plus your **Additional Terms to Drop**, minus your **Terms to Ignore**. - **Drop from Suffix** (on by default): removes terms from the end of the entity name. - **Drop from Prefix**: removes terms from the beginning of the entity name. - **Drop from Interior**: removes terms from anywhere within the entity name, including the beginning and the end. |
| **Additional Terms to Drop** | Custom terms added to the built-in list. They are removed only in the positions whose switch is on. |
| **Terms to Ignore** | Specifies terms that should be excluded from the cleaning process and retained in the final output. |
| **Additional Metadata** | Enables you to add custom metadata to enhance the computed field definition, such as descriptions or key-value attributes. |

### Example for Cleaned Entity Name

| Example | Input | Transformation | Output |
| --- | --- | --- | --- |
| 1 | `"TechCorp, Inc."` | **Drop from Suffix** on (the default) | `"TechCorp"` |
| 2 | `"The Global Group"` | **Drop from Prefix** on, **Additional Terms to Drop**: `The` | `"Global Group"` |
| 3 | `"Central LTD & Finance Co."` | Only **Drop from Interior** on | `"Central & Finance"` |
| 4 | `"Eat & Drink LLC"` | **Drop from Suffix** and **Drop from Interior** on, **Additional Terms to Drop**: `&` | `"Eat Drink"` |
| 5 | `"Acme Trading Company"` | **Drop from Suffix** on, **Terms to Ignore**: `Company` | `"Acme Trading Company"` |

> **Note**
>
> Matching ignores periods, commas, and hyphens, so the built-in list can hold several spellings of one term (for example `Ltd`, `Ltd.`, and `L.T.D.`). To keep such a term, add every spelling to **Terms to Ignore**. Ignoring only `Ltd.` still lets the other spellings match and remove it.

## Convert Formatted Numeric

The **Convert Formatted Numeric** transformation turns formatted text such as `"$1,234.56"` into a number. It strips characters that are not part of the number, like commas, currency symbols, and percent signs, and reads a value wrapped in parentheses as negative. The result is a numeric field.

### Options for Convert Formatted Numeric

| Field | Description |
| --- | --- |
| **Name** | Specifies the name of the computed field. This name is used to identify the computed field. |
| **Transformation Type** | Select **Convert Formatted Numeric** to convert formatted numeric values into a standard numeric format. |
| **Field** | Selects the source field on which the transformation is applied. |
| **Additional Metadata** | Allows adding custom metadata to enhance the computed field definition (for example, descriptions or key-value attributes). |

### Example for Convert Formatted Numeric

| Example | Input | Transformation | Output |
| --- | --- | --- | --- |
| 1 | `"$1,234.56"` | Strip `$` and `,` | `1234.56` |
| 2 | `"(2020)"` | Parentheses mark a negative value | `-2020` |
| 3 | `"100%"` | Strip `%` | `100` |
| 4 | `"N/A"` | Nothing numeric remains | `null` |

A value that cannot be read as a number after the extra characters are stripped becomes `null`, so it shows up in completeness statistics rather than failing the profile.

## Custom Expression

The **Custom Expression** transformation creates a new field based on a Spark SQL expression. Use it for logic that is not covered by the other transformation types: combining fields, applying conditional logic, or using window functions.

### Options for Custom Expression

| Field | Description |
| --- | --- |
| **Name** | Specifies the name of the computed field. This name is used to identify the computed field. |
| **Transformation Type** | Select **Custom Expression** to define a computed field using a Spark SQL expression. |
| **SQL Expression** | Enter the Spark SQL logic used to compute the field. Press `Ctrl`+`Space` to view available hints and functions. |
| **Additional Metadata** | Allows adding custom metadata to enhance the computed field definition (for example, descriptions or key-value attributes). |

### Using Custom Expression

You can combine multiple fields, apply conditional logic, or use any valid Spark SQL function to derive your new computed field.

**Example**: to create a field that adds two existing fields in each row, use the expression `field1 + field2`.

The expression produces one value per row, so an aggregate function such as `SUM` or `AVG` works only as a window function with an `OVER` clause, for example `SUM(amount) OVER (PARTITION BY customer_id)`. A bare `SUM(amount)` fails validation when you save.

### Advanced Example: Detecting Overlapping Leases

Consider a log of leases where each row captures a single lease's details:

| LeaseID | AssetID | Lease_Start | Lease_End |
| --- | --- | --- | --- |
| 1 | 42 | 1/1/2025 | 2/1/2026 |
| 2 | 43 | 1/1/2025 | 2/1/2026 |
| 3 | 42 | 1/1/2026 | 2/1/2026 |
| 4 | 43 | 2/2/2026 | 2/1/2027 |

**Lease 1** overlaps with **Lease 3** for the same Asset (`42`). This is hard to detect without transforming the data. A Computed Field named `Next_Lease_Start` using the following custom expression produces the value of the next lease's start date for each asset:

```sql
LEAD(Lease_Start, 1) OVER (PARTITION BY AssetID ORDER BY Lease_Start)
```

The table now includes the derived field:

| LeaseID | AssetID | Lease_Start | Lease_End | Next_Lease_Start |
| --- | --- | --- | --- | --- |
| 1 | 42 | 1/1/2025 | 2/1/2026 | 1/1/2026 |
| 2 | 43 | 1/1/2025 | 2/1/2026 | 2/2/2026 |
| 3 | 42 | 1/1/2026 | 2/1/2026 |  |
| 4 | 43 | 2/2/2026 | 2/1/2027 |  |

With `Next_Lease_Start` in place, you can author a quality check stating that **Lease_End** must always be less than **Next_Lease_Start** to catch overlaps. Qualytics AI will automatically generate that check for you at [AI Effort = High](https://userguide.qualytics.io/operations/profile/profile/#high-time-series-and-comparative-relationship-checks).

### More Examples for Custom Expression

| Example | Input Fields | Custom Expression | Output |
| --- | --- | --- | --- |
| 1 | `field1 = 10`, `field2 = 20` | `field1 + field2` | `30` |
| 2 | `salary = 50000`, `bonus = 5000` | `salary + bonus` | `55000` |
| 3 | `hours = 8`, `rate = 15.50` | `hours * rate` | `124` |
| 4 | `status = 'active'`, `score = 85` | `CASE WHEN status = 'active' THEN score ELSE 0 END` | `85` |

*Last updated: October 20, 2026.*