Not available in Community Designer

In Pivot, you can write the transformation function yourself. That can be done either in CTL or Java, see Advanced attributes in Pivot Attributes

Before writing the transformation, you might want to refer to some of the sections touching the subject:

Let us have the following input txt file with comma-separated values:

Because we are going to group the data according to the groupID field, the input has to be sorted (mind the ascending order of groupIDs). In the Pivot component, we will make the following settings:

Group key = groupID (to group all input records with the same groupID)

Field defining output field name = fieldName (to say we want to take output fields' names from this input field)

Field defining output field value = fieldValue (to say we want to take output fields' values from this input field)

Processing that data with Pivot produces the following output:

Notice the input recordNo field has been ignored. Similarly, the output comment had no corresponding fields on the input, that is why it remains null. groupID makes part in the output metadata and thus was copied automatically.

	Note
	If the input is not sorted (not like in the example), grouping records according to their count is especially handy. Omit Group key and set Group size instead to read sequences of records that have exactly the number of records you need.

Input metadata have fields filedName and fieldValue. The records contain timestamp, IP address and username.

timestamp|2014-10-30 13:51:12
address  |192.168.10.15
username |Alice
timestamp|2014-10-30 13:52:14
address  |192.168.3.151
username |Bob
timestamp|2014-10-30 13:52:40
address  |192.168.102.105
username |Eve

Convert the data to one line structure having timestamp, IP address and username.

Solution

Use attributes Group size, Field defining output field name and Field defining output field value.

Output metadata has to have fields timestamp, address and user.

This example is similar to previous one: input records contain timestamp, IP address and username, but there is no field saying which one is timestamp, IP address or username. The order of the input records within the group is always the same: timestamp is before IP address and IP address is before username.

2014-10-30 13:51:12
192.168.10.15
Alice
2014-10-30 13:52:14
192.168.3.151
Bob
2014-10-30 13:52:40
192.168.102.105
Eve

Solution

One output record correspond to three input records, so we use Group size attribute. Mapping to the output record is defined in Pivot transformation.

//#CTL2

function integer getOutputFieldIndex(integer idx) {
 	return idx % 3;
}

function string getOutputFieldValue(integer idx) {
 	return $in.0.value;
}

The order of input records corresponds to the order of output metadata fields. If you need a different order, rearrange the output metadata or change the content of getOutputFieldIndex() function.

The input records have customerId, batchId, fieldName and value metadata fields:

C0001|B001|firstName|John
C0001|B001|lastName |Doe
C0001|B001|accountNo|A0001

Convert data to following the format:

C0001|B001|John|Doe|A0001

Solution

Note that Group key fields have been passed to the corresponding output fields.

Originally, a transformation executed in compiled CTL mode when Field defining output field name or Field defining output field value was not set finished successfully, but did not produce any output.

Such transformation should now fail in init(), just like in interpreted mode.

Additionally, getOutputFieldValue() can be overridden also in compiled CTL mode - the implementation is no longer ignored. If the function raises an error, the transformation fails.