Add/ Edit Virtual Fields

To open the Add/ Edit Virtual Fields screens, click a Field or the Click to add field link on the Virtual Fields tab.

The Add/ Edit Virtual Fields screens allow you to add or edit virtual fields on a new or existing query in the Data Miner utility.

The following fields appear on the Add/Edit Virtual Fields screens:

Note:  The fields that are displayed will vary depending on the Type and Operator of the selected virtual field.

Field Description
Virtual Field Name

The name of the field that will be added to the query.

Note:  You cannot include spaces in the virtual field name.
Type

The type of values that will be displayed in the field; either Numbers or Characters.

Note:  After selecting a Type, the Add/ Edit Virtual Fields screens will refresh, displaying a new set of criteria for the virtual field.

Note:  For numeric virtual fields, any divisors that equate to 0 when calculating the cell values will be 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 would naturally equate to (100 / 0). In this case, the system will automatically substitute 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.

Note:  If the cell values of the virtual field exceed the maximum number of characters specified here, no output will be generated.

Note:  If you leave this field blank, the system will automatically default 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.

Note:  This field will only appear if Number is selected in the Type field.

Note:  If the virtual field cell values contain more decimal characters than the maximum amount specified in this field, the system will automatically round up the value to the closest decimal place.

Note:  If you leave this field blank, the system will automatically default 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.

Note:  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 be used with the selected fields/values to calculate the cell values of the virtual field. The options displayed is this field will vary depending on the selected virtual field Type:

  • If Number is selected in the Type field, the available operators allow you to add (+), subtract (-), multiply (*), or divide (/) the selected fields/values.
  • If Character is selected in the Type field, the available operators allow you to Concatenate the selected fields/values, create Substrings of the selected field values (allowing you to specify the exact number of characters to display in the cell values), Trim a specific character from the selected field values, Replace a specific set of characters from the selected field values, or add Spaces to the selected field values.

Note:  If you are creating a Character- based virtual field and you select the Substring, Trim or Replace operator, a new set of fields will appear. See below for details.

The following fields will only appear in the Expression section of the Add/Edit Virtual Fields screens if you are adding or editing a Substring virtual field:

Field Description
Field The field within the query file from which all of the cell values of the virtual field will be derived from.
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 will only include characters 6,7, and 8 in the cell values of the virtual field.

Note:  If you leave this field blank, the system will automatically default to the length of the largest known cell value after considering the Start At position.

The following fields will only appear in the Expression section of the Add/Edit Virtual Fields screens if you are adding or editing a Trim virtual field:

Field Description
Field The field within the query file from which all of the cell values of the virtual field will be derived from.
Position

The position from which the specified Removal Character will be removed from the cell values of the selected Field; either the beginning (Leading), end (Trailing), or Both.

Note:  If left blank, this field will default 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, simply select Leading in the Position field and type J in the Removal Character field. The system will automatically remove the letter J from the beginning of the cell values of the virtual field upon running the query.

Note:  To remove a space from the selected Field values, simply leave this field blank.

The following fields will only appear in the Expression section of the Add/Edit Virtual Fields screens if you are adding or editing a Replace virtual field:

Field Description
Field The field within the query file from which all of the cell values of the virtual field will be derived from.
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, simply enter Mike in the String Pattern field and Michael in the Replace With field. The system will automatically perform the replacement upon running the query.

The following buttons allow you to complete your work on the Add/ Edit Virtual Fields screens:

Button Function
Save

Saves the selected virtual field criteria.

Note:  This button will only appear when editing an existing virtual field.
Save & New Saves the selected virtual field criteria, adds it to the query, and refreshes the screen allowing you 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.

Note:  This button will only appear when editing an existing virtual field.
Close Closes the Add/ Edit Virtual Fields screen without saving any changes.