RTE operation types included within the Integration Commons for CMDB app

The Robust Transform Engine (RTE) operation types are common operation methods for use in ETL without having to write your own complex data transformations.

The following operation types are available in the Integration Commons for CMDB app.

Concatenation

Combines the values from input fields into a single string, joining them on the optional joining_string field.

Details
Table	RTE Entity Concatenation Operation [sys_rte_eb_concat_operation]
Input field	source_sys_rte_eb_fields Input is a set of fields and constants.
Output field	target_sys_rte_eb_field Output is the combined value of the provided fields as a single string.
Additional fields	joining_string (optional)

Table 1. Example

Input	joining_string	Result
"input_1", "input_2", "input_3"	", "	"input_1, input_2, input_3"

Convert to Boolean

Converts the incoming value to a Boolean.

Details
Table	RTE Entity Convert to Boolean Operation [sys_rte_eb_to_boolean_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is true when input is true or 1, otherwise output is false. (case insensitive)

Table 2. Example

Input	Result
input_1	false
true	true
1	true
0	false
11	false

Convert to Date

Attempts to convert the incoming value to a GlideDateTime value by applying the date_format to the incoming value.

Details
Table	RTE Entity Convert to Date Operation [sys_rte_eb_to_date_operation]
Input field	source_sys_rte_eb_field Input is a data timestamp value with date format.
Output field	target_sys_rte_eb_field Output is the date timestamp in the specified date format. Attempts to directly convert using GlideDateTime if the date_format is incorrect. Returns an empty value if unable to parse at all.

Table 3. Example

Input	Result
"2018/09/20 11:21:00 AM EST" with date_format "yyyy/MM/dd hh:mm:ss a z"	"2018-09-20 16:21:00"
"2018/09/20 01:21:00 PM EST" with date_format "yyyy/MM/dd hh:mm:ss a z"	"2018-09-20 18:21:00"
"09/20/18" with date_format "yyyy/MM/dd hh:mm:ss a z"	""0018-09-20 00:00:0"

Convert to Numeric

Converts the incoming value to a number.

Details
Table	RTE Entity Convert to Numeric Operation [sys_rte_eb_to_numeric_operation]
Input field	source_sys_rte_eb_field Input is a value.
Output field	target_sys_rte_eb_field Output is a numeric value. If the input value is non-numeric the output is empty.

Table 4. Example

Input	Result
input_1	null
1.23	1.23
1.00	1
two	null

Copy

Copies the value of the source field to all the target fields.

Details
Table	RTE Entity Copy Operation [sys_rte_eb_copy_operation]
Input field	source_sys_rte_eb_field Input is a value.
Output field	target_sys_rte_eb_fields Output is the copied source field value.
Additional field	overwrite_existing_value (optional, Boolean): If true, then the values of the target fields are replaced. Otherwise, any non-empty value isn’t overwritten.

Extract First Numeric

Sets the target field as the first numeric value found in the source field.

Details
Table	RTE Extract Numeric Operation [sys_rte_eb_extract_numeric_operation]
Input field	source_sys_rte_eb_field Input is a value.
Output field	target_sys_rte_eb_field Output is the numeric value found in the input.
Additional fields	decimal_places (optional, number) : Forces the output to have a specified number of decimal places. remainder_target_field (optional, reference to field): Set to the trimmed remainder of the source field, after removing the first numeric value.

Table 5. Example

Input	Result
100 mb	100
100.123 mb	100.123
100.123 mb with decimal_places=2	100.12
100 mb with decimal_places=2	100.00
100 mb with remainder_target_field	mb

Glide Lookup Operation

Performs a lookup in the database on the target table specified in the target_table field.

Details
Table	RTE Glide Lookup Operation [sys_rte_eb_glide_lookup_operation]
Input field	source_sys_rte_eb_fields The database table for lookup.
Output field	target_sys_rte_eb_fields The resulting data based on the lookup operation.
Additional fields	target_table glide_matching_fields (string): Comma-separated list of column names in the target table. For each input field in source_sys_rte_eb_fields, there must be an equal number of values in glide_matching_fields. glide_target_fields (string): Comma-separated list of column names in the target table. For each target field in target_sys_rte_eb_fields, there must be an equal number of values in glide_target_fields.

Table 6. Example

Input	Result
Input Field 1: 100 South Charles Street, Baltimore Input Field 2: MD Target Table: Location (cmn_location) Glide Matching Fields: street,state Glide Target Fields: sys_id	Output Field 1: 25ab9c4d0a0a0bb300f7dabdc0ca7c1c

Multiple Input Script 

Runs a script with multiple inputs setting the target_sys_rte_eb_field field as the output for that script.

Details
Table	RTE Entity Multiple Input Script Operation [sys_rte_eb_multi_in_script_operation]
Input field	source_sys_rte_eb_fields Input is a script.
Output field	target_sys_rte_eb_field Output is the result of the input script.
Additional Fields	script (script) use_unique_input_sets(Boolean): When true, only unique input values are included in the data batch for IRE processing. Otherwise, all input object’s field values are included.

Example for using use_unique_input_sets, with a script function that takes record_type and operating_system as input and returns record_with_os:

Table 7. Examples

Record	record_type	operating_system
1	computer	Windows XP
2	computer	Linux
3	computer	Windows XP

If use_unique_inputs_sets is set to true, then the script processes only two values (computer + Windows XP and computer + Linux). If use_unique_inputs_sets is set to false, then each of the three values is individually processed (computer + Windows XP, computer + Linux, and computer + Windows XP).

Sample script:

(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        // batch[i] is the unique set of inputs/individual record 
                        // batch[i].<field> gives access to the field value 
                        var in0 = gs.nil(batch[i].record_type) ? '' : batch[i].record_type;
                        var in1 = gs.nil(batch[i].operating_system) ? '' : batch[i].operating_system;
                        // output[i] is the output for the specific combination of inputs/individual record 
                        output[i] = in0 + "_" + in1; 
                    } 
                } 
            })(batch, output);

Multiple Input/Output Script

Runs a script with multiple inputs setting the target fields specified in the target_sys_rte_eb_fields field as the multiple outputs for that script.

Details
Table	RTE Entity Multiple Input/Output Script Operation [sys_rte_eb_multiple_input_output_script_operation]
Input field	source_sys_rte_eb_fields Input is a script.
Output field	target_sys_rte_eb_fields Output is the result of the input script.
Additional Fields	script (script)

Sample script:

(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        var userId = (batch[i].user_id);
                        var userIdParts = userId.split(".");
                        output[i].first_name = userIdParts[0]; 
                        output[i].last_name = userIdParts[1];
                    } 
                } 
            })(batch, output);

Regex Replace 

Replaces each substring of the input string that matches the regular expression pattern specified in the match_regex field with the string specified in the replacement_regex field.

Details
Table	RTE Entity Regular Expression Replace Operation [sys_rte_eb_regex_replace_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is the replaced string.
Additional fields	match_regex (string, regular expression) replacement_regex (string)

Table 8. Example

Input	Result
"String&With(Special)$Characters" with match_regex="[^0-9a-zA-Z]+" and replacement_regex=" "	"String With Special Characters"

Replace 

Replaces each substring of the input string that matches the string specified in the match_string field with the string specified in the replacement_string field.

Details
Table	RTE Entity Replace Operation [sys_rte_eb_replace_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is the replaced string.
Additional fields	match_string (string) replacement_string (string)

Table 9. Example

Input	Result
"Original String" with match_string = "Original" and replacement_string= "Replacement"	"Replacement String"

Round Numeric

Rounds off the input numeric value to the nearest whole number.  Non-numbers are truncated.

Details
Table	RTE Entity Round Numeric Operation [sys_rte_eb_round_numeric_operation]
Input field	source_sys_rte_eb_field Input is a numeric value.
Output field	target_sys_rte_eb_field Output is a whole number.
Additional fields	match_string (string) replacement_string (string)

Table 10. Example

Input	Result
"1.5"	"2"
"1.4"	"1"
"i’m a string"	""

Script Operation

Runs a script with an input and sets the target_sys_rte_eb_field field as the output for that script.

Note:

The Script operation type has been largely superseded by the Multi Input Script Operation and is included for backwards compatibility with the existing configurations. 

Details
Table	RTE Entity Script Operation [sys_rte_eb_script_operation]
Input field	source_sys_rte_eb_field Input is a script.
Output field	target_sys_rte_eb_field Output is the result of the input script.
Additional fields	script(script) use_unique_input_sets (Boolean): When true, only unique input values are included in the data batch for IRE processing. Otherwise, all input object’s field values are included. For an example and for more details, see the Multiple Input Script transform.

The source field is included in the ‘batch’ variable as the JavaScript field ‘input’.

(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        // batch[i] is the unique set of inputs/individual record 
                        // batch[i].input gives access to the field value 
                        var in0 = gs.nil(batch[i].input) ? '' : batch[i].input; 
                        // output[i] is the output for the specific combination of inputs/individual record 
                        output[i] = in0 + " modified by script"; 
                    } 
                } 
            })(batch, output); 

Example:

/* Example Script
 (function(batch, output) {
     for (var i = 0; i < batch.length; i++) {
         //step1: access the input variables
         var a = batch[i].input; //Value of the source field.
 
         //step2: Your script/code goes here.
         var b = a + 1;
         //step3: set the output for each elements
         output[i] = b;
     }
 })(batch, output);
*/ 

Set 

Sets the target_sys_rte_eb_field field value to the string provided in the set_value field.

Details
Table	RTE Entity Set Operation [sys_rte_eb_set_operation]
Output field	target_sys_rte_eb_field Output is the value associated with the set_value field.
Additional fields	set_value (string) overwrite_existing_value (optional, Boolean) : When true, the current value of the target field is overwritten. Otherwise, a non-empty value isn't replaced.

Set Min/Max

Sets the target field to either the maximum or minimum of the values from all input fields.

Details
Table	RTE Entity Min/Max Operation [sys_rte_eb_min_max_operation]
Input field	source_sys_rte_eb_fields Input is a set of values.
Output field	target_sys_rte_eb_field Output is the maximum or minimum value based on the min_max value.
Additional fields	data_type (choice list with values as STRING, NUMERIC, and DATE) min_max (choice list with values as MIN and MAX)

Table 11. Examples

Input	Output
"2", "-1", "0"	2
"a", "b"	c
"2", "-1", "0"	-1
"a", "b"	a

Split 

Splits the string included in the source_sys_rte_eb_field input value at the separator specified in the splitting_string field and assigns the resulting array of strings to the target_sys_rte_eb_field field, in order.

Details
Table	RTE Entity Split Operation [sys_rte_eb_split_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_fields Output is list of substrings.
Additional fields	splitting_string (string)

Table 12. Example

Input	Result
"value1||value2||value3", splitting_string:"||" with target_sys_rte_eb_fields {target1,target2,target3}	target1 : value1, target2 : value2, target3 : value3
"value1||value2||value3", splitting_string:"||" with target_sys_rte_eb_fields {target1}	target1 : value1
"value1", splitting_string:"||" with target_sys_rte_eb_fields {target1,target2,target3}	target1 : value1, target2 : <null>, target3 : <null>

Trim 

Removes any whitespaces at the beginning and at the end of the string included in the source_sys_rte_eb_field input value and assigns the result to the target_sys_rte_eb_field field.  This transform is equivalent to the Java String trim() Method.

Details
Table	RTE Entity Trim Operation [sys_rte_eb_trim_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is the input string value but without any leading and trailing spaces.

Table 13. Example

Input	Result
" value 1 "	"value 1"

Uppercase

Changes all characters of the source_sys_rte_eb_field input value to upper case and assigns the result to the target_sys_rte_eb_field field.

Details
Table	RTE Entity Upper Case Operation [sys_rte_eb_upper_case_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is the upper case string value.

Table 14. Example

Input	Result
"value1"	"VALUE1"

Uppercase Trim 

Combines both the Uppercase and the Trim transforms.

Details
Table	RTE Entity Upper Case Trim Operation [sys_rte_eb_upper_case_trim_operation]
Input field	source_sys_rte_eb_field Input is a string value.
Output field	target_sys_rte_eb_field Output is the upper case string value without any whitespaces at the beginning and end.

Table 15. Example

Input	Result
"      value1    "	"VALUE1"