Creating a Custom Transform

The Transformer stage has predefined transforms that perform a variety of common data transformations. If the predefined transforms do not meet your needs, you can write a custom transform script using Groovy. This procedure describes how to create basic custom transforms using Groovy. For complete documentation on Groovy, see groovy-lang.org.

  1. In Spectrum Enterprise Designer, add a Transformer stage to the dataflow.
  2. Double-click the Transformer stage.
  3. Click Add.
  4. Under General, click Custom.
  5. In the Custom transform name field, enter a name for the transform you will create. The name must be unique.
  6. Click Script Editor.

    This editor provides a variety of features to make developing your transform easier, such as code completion and palettes listing functions and fields.

    TaskInstructions

    To add a function

    In the Functions pane, double-click the function you want to add.

    Note: The functions listed in the editor are functions provided to make writing custom transform scripts easier. They perform functions that would otherwise require multiple lines of Groovy code to accomplish. They are not standard Groovy functions.

    To get the value from a dataflow field

    		 In the Input Fields pane, double-click the input field you want. The following will be added to your script:
    data['FieldName']

    For example, if you want to get the value from the field CurrentBalance, the following would be added:

    data['CurrentBalance']

    To set the value of a dataflow field

    Enter this code in the script editor:

    data['FieldName']=NewValue

    For example, to set the field Day to the day of the week contained in the field PurchaseDate:

    data['Day']=dayOfWeek(data['PurchaseDate'])

    In this example, the function dayOfWeek() is used to get the day from the date value in the PurchaseDate field, and the result is written to the Day field.

    Tip: You can double-click the name of the output field in the Output Fields pane to add the field reference to the script.

    To change the scope of a script variable in a dataflow

    		 To change the scope of a script variable from a single input record to all the input records in a dataflow, use the @Field annotation in your script as shown:
    import groovy.transform.Field;
@Field ['data type']['VariableName']= Value;
    For example, to set the scope of a variable RecordNumber to a single input record, specify this:
    int recordNumber = 1;
data['Record_Number']= recordNumber;
recordNumber++;

    The output will be:

    To change the scope of this variable to all input records, specify this:
    import groovy.transform.Field 
@Field int recordNumber = 1;
data['Record_Number']= recordNumber;
recordNumber++;

    The output will be:

    To create a new field using a numeric data type

    Enter this code in the script editor:

    data['FieldName'] = new constructor;

    Where constructor is one of these:

    java.lang.Double(number)
    Creates a field with a data type of Double.
    java.lang.Float(number)
    Creates a field with a data type of Float.
    java.lang.Integer(number)
    Creates a field with a data type of Integer. You can also create a new integer field by specifying a whole number. For example, this will create an integer field with a value of 23:
    data['MyNewField'] = 23;
    java.lang.Long(number)
    Creates a field with a data type of Long.

    For example, to create a new field named "Transactions" with a data type of Double and the value 23.10, you would specify the following:

    data['Transactions'] = new com.java.lang.Double(23.10);

    To create a new field using a date or time data type

    Enter this code in the script editor:

    data['FieldName'] = new constructor;

    Where constructor is one of these:

    com.pb.spectrum.api.datetime.Date(year,month,day)
    Creates a field with a data type of date. For example, December 23, 2013 would be: 
    2013,12,23
    com.pb.spectrum.api.datetime.Time(hour,minute,second)
    Creates a field with a data type of time. For example, 4:15 PM would be:
    16,15,0
    .
    com.pb.spectrum.api.datetime.DateTime(year,month,day,hour,minute,second)
    Creates a field with a data type of DateTime. For example, 4:15 PM on December 23, 2013 would be:
    2013,12,23,16,15,0

    For example, to create a new field named "TransactionDate" with a data type of Date and the value December 23, 2013, you would specify this:

    data['TransactionDate'] = new com.pb.spectrum.api.datetime.Date(2013,12,23);
    To create a new field with a data type of Boolean

    Enter this code in the script editor:

    data['FieldName'] = true or false;

    For example, to create a field named IsValidated and set it to false, you would specify this:

    data['IsValidated'] = false;
    To create a new list field

    Use the factory.create() method to create new fields in a record then use the leftShift operator << to append the new record to the list field.

    NewListField = []

NewRecord = factory.create()
NewRecord['NewField1'] = "Value"
NewRecord['NewField12'] = "Value"
...
NewListField << NewRecord

NewRecord = factory.create()
NewRecord['NewField1'] = "Value"
NewRecord['NewField12'] = "Value"
...
NewListField << NewRecord
data['ListOfRecords'] = NewListField

    For example, this creates a new list field called "addresses" consisting of two "address" records.

    addresses = []
address = factory.create()
address['AddressLine1'] = "123 Main St"
address['PostalCode'] = "12345"
addresses << address

address = factory.create()
address['AddressLine1'] = "PO Box 350"
address['PostalCode'] = "02134"
addresses << address
data['Addresses'] = addresses

    You can also create a new list field that contains a list of individual fields rather than a list of records. For example, this creates a new list field called PhoneNumbers containing home and work phone numbers:

    phoneNumbers = []
phoneNumbers << data['HomePhone']
phoneNumbers << data['WorkPhone']
data['PhoneNumbers'] = phoneNumbers

    To concatenate fields

    Use the + symbol. For example, this example concatenates the FirstName field and the LastName field into a value and stores it in the FullName field

    String fullname = data['FirstName'] + ' ' + data['LastName'];
data['FullName']=fullname;

    In this example there are two input fields (AddressLine1 and AddressLine2) which are concatenated and written to the output field Address.

    address1 = data['AddressLine1']; 
address2 = data['AddressLine2']; 
data['Address']=address1+ ',' + address2;

    To parse a field

    Identify a separation character then use substring to parse the field. In this example, if the PostalCode field is greater than five characters, it separates the five-character ZIP Code and the +4 portion and writes them to separate fields in the output record. 

    if (data['PostalCode'].length() > 5) 
{ 
	 String postalCode = data['PostalCode']; 
	 int separatorPosition = postalCode.indexOf('-'); 
	 String zip = postalCode.substring(0, separatorPosition); 
	 String plusFour = postalCode.substring( 
			 separatorPosition + 1, 
			 postalCode.length(); 
	 data['Zip']=zip; 
 	data['PlusFour']=plusFour; 
}

    To perform conditional processing

    Use an if or switch statement. These are the most common conditional processing constructs. For more information see groovy-lang.org.

    This example sets the field AddressCity to the first address line and city name if the city is Austin. 

    city = data['City']; 
address1 = data['AddressLine1'] 
if(city.equals('Austin')) 
data['AddressCity']=address1 +',' + city;

    To perform looping

    Use the for loop. This is the only looping construct you should need. For more information about looping or syntax see groovy-lang.org.

    To augment data

    Define a constant and use the concatenation character +. For example, this script appends the word "Incorporated" to the FirmName field. 

    firmname = data['FirmName']; 
constant = 'Incorporated'; 
if(firmname.length() > 0)
data['FirmName']=firmname + ' ' + constant;

    To access an option specified at runtime

    If the dataflow has runtime options enabled, you can access settings passed to the dataflow at runtime by using this syntax:

    options.get("optionName")

    For example, to access an option named casing, you would include this in your custom transform script:

    options.get("casing")
  7. After you are done entering your script, click the "X" button in the window to close the editor.
  8. In the Input fields field, select the field or fields to which you want to apply the transform.
  9. In the Output fields field, specify the field to which you want to write the output from the transform. If necessary, you can define a new field by clicking the Add button to the right of the Output fields field.
  10. When you are done, click the Add button at the bottom of the window.
  11. Click OK.