Add, Edit Virtual Fields
Use the Add/Edit Virtual Fields screens to add or edit virtual fields on a new or existing query in the Data Miner utility. The fields that are displayed vary depending on the Type and Operator of the selected virtual field.
To open the Add/Edit Virtual Fields screens, click a Field or the Click to add field link on the Virtual Fields tab.
The fields on the Add/Edit Virtual Fields screens are:
| Field | Description |
|---|---|
| Virtual Field Name | The name of the field that is added to the query. You cannot include spaces in the virtual field name. |
| Type |
The type of values that are displayed in the field; either Numbers or Characters. After selecting a Type, the Add/Edit Virtual Fields screens refresh, displaying a new set of criteria for the virtual field. For numeric virtual fields, any divisors that equate to 0 when calculating the cell values are converted to 1. For example, if you have a virtual field named VF1 where the calculation is (15 - 15), and a second virtual field where the calculation is (100 / VF1), it naturally equates to (100 / 0). In this case, the system automatically substitutes 1 for the divisor, making the resulting cell value 100. |
| Length |
The maximum number of characters that can be displayed in the virtual field cells. If the cell values of the virtual field exceed the maximum number of characters specified here, no output is generated. If you leave this field blank, the system automatically defaults to the largest known cell value based on the virtual field criteria. |
| Decimals |
The maximum number of decimal characters that can be displayed in the virtual field cells. This field only appears if Number is selected in the Type field. If the virtual field cell values contain more decimal characters than the maximum amount specified in this field, the system automatically rounds up the value to the closest decimal place. If you leave this field blank, the system automatically defaults to the largest known decimal value in the virtual field. |
| Field/Value |
The field or value you want to use in calculating the cell values for the virtual field. You can add up to 10 separate fields to calculate the values of the virtual field (depending on the Operator); however, if more are required, you can create a second virtual field that uses the original virtual field in the new calculation. |
| Operator |
The operator to use with the selected fields/values to calculate the cell values of the virtual field. The options displayed in this field vary depending on the selected virtual field Type:
If you are creating a Character-based virtual field and you select the Substring, Trim, or Replace operator, a new set of fields appears. See below for details. |
The fields in the Expression section of the Add/Edit Virtual Fields screens when adding or editing a Substring virtual field are:
| Field | Description |
|---|---|
| Field | The field within the query file from which all of the cell values of the virtual field are derived. |
| Start At | The character in the cell values of the selected Field (when calculated from left to right) that you want the cell values of the virtual field to begin from. |
| Substring Length | The number of characters you want to include in the cell values of the virtual field. For example, assuming the cell values of the selected Field all contain 8 characters and you enter 5 in the Start At field and 3 in the Substring Length field, upon running the query the system only includes characters 6, 7, and 8 in the cell values of the virtual field. If you leave this field blank, the system automatically defaults to the length of the largest known cell value after considering the Start At position. |
The fields in the Expression section of the Add/Edit Virtual Fields screens when adding or editing a Trim virtual field are:
| Field | Description |
|---|---|
| Field | The field within the query file from which all of the cell values of the virtual field are derived. |
| Position | The position from which the specified Removal Character is removed from the cell values of the selected Field; either the beginning (Leading), end (Trailing), or Both. If left blank, this field defaults to Both. |
| Removal Character | The character you want to remove from the cell values of the selected Field. For example, assuming the cell values of the selected Field all begin with the letter J and you want to remove it for the cell values of the virtual field, select Leading in the Position field and type J in the Removal Character field. The system automatically removes the letter J from the beginning of the cell values of the virtual field upon running the query. To remove a space from the selected Field values, leave this field blank. |
The fields in the Expression section of the Add/Edit Virtual Fields screens when adding or editing a Replace virtual field are:
| Field | Description |
|---|---|
| Field | The field within the query file from which all of the cell values of the virtual field are derived. |
| String Pattern | The characters you want to replace in the cell values of the selected Field. |
| Replace With | The characters you want to replace the selected String Pattern with. For example, assuming some of the cell values of the selected Field contain the name Mike and you want to replace it with Michael in the cell values of the virtual field, enter Mike in the String Pattern field and Michael in the Replace With field. The system automatically performs the replacement upon running the query. |
Use these buttons to complete your work on the Add/Edit Virtual Fields screens:
| Button | Description |
|---|---|
| Save | Saves the selected virtual field criteria. This button only appears when editing an existing virtual field. |
| Save & New | Saves the selected virtual field criteria, adds it to the query, and refreshes the screen to create another virtual field. |
| Save & Close | Saves the selected virtual field criteria, adds it to the query, and returns to the Virtual Fields tab. |
| Delete | Deletes the selected virtual field. This button only appears when editing an existing virtual field. |
| Close | Closes the Add/Edit Virtual Fields screen without saving any changes. |
Security: 505 - Data Miner
Revision: 2026.01