Creating a Custom Transform

The Transformer stage has predefined transforms that perform multiple 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.
Note: Review this topic to learn how to select fields for processing: Creating a Custom Transform.
  1. In Flow Designer, add a Transformer stage to the dataflow.
  2. Double-click the Transformer stage to display the Transformer Options page.
  3. Using the palette on the left side of the page:
    1. Select one or more Input fields to which you will apply the transform.
    2. 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 NEW in the field list.
  4. Click in the Script Editor workspace.

    The editor provides an easy-to-use workspace to develop your custom transforms, which includes type-ahead and selections. As you type in the workspace, Flow Designer provides applicable parameters based on your entries.

  5. Use this reference as a guideline for constructing your custom transformation scenarios.
    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++;
    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++;

    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")
  6. After you are done entering your script, click Apply to save your work.