Pivot
We suppose that you have already learned what is described in:
If you want to find the appropriate Transformer for your purpose, see Transformers Comparison.
Short Summary
Pivot produces a pivot table. The component creates a data summarization record for every group of input records. A group can be identified either by a key or its size.
| Component | Same input metadata | Sorted inputs | Inputs | Outputs | Java | CTL |
|---|---|---|---|---|---|---|
| Pivot | - | no | 1 | 1 | yes | yes |
Note: When using the key attribute, input records should be sorted, though. See Advanced Description.
Abstract
The component reads input records and treats them as groups. A group is defined either by a key or a number of records forming the group. Pivot then produces a single record from each group. In other words, the component creates a pivot table.
Pivot has two principal attributes which instruct it to treat some input values as output field names and other inputs as output values.
The component is a simple form of Denormalizer.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | yes | For input data records | Any1 |
| Output | 0 | yes | For summarization data records | Any2 |
Pivot Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| Key | 1) | The key is a set of fields used to identify groups of input records (more than one field can form a key). A group is formed by a sequence of records with identical key values. | any input field |
| Group size | 1) | The number of input records forming one group. When using Group size, the input data do not have to be sorted. Pivot then reads a number of records and transforms them to one group. The number is just the value of Group size. | <1; n> |
| Field defining output field name | 2) | The input field whose value "maps" to a field name on the output. | |
| Field defining output field value | 2) | The input field whose value "maps" to a field value on the output. | |
| Sort order |
Groups of input records are expected to be sorted in the order
defined here. The meaning is the same as in Denormalizer,
see
Sort order.
Beware that in Pivot, setting this to
Ignore can produce unexpected results if
input is not sorted.
| Auto (default) | Ascending | Descending | Ignore | |
| Equal NULL | Determines whether two fields containing null values are considered equal. | true (default) | false | |
| Advanced | |||
| Pivot transformation | 3) | Using CTL or Java, you can write your own records transformation here. | |
| Pivot transformation URL | 3) | The path to an external file which defines how to transform records. The transformation can be written in CTL or Java. | |
| Pivot transformation class | 3) | The name of a class that is used for data transformation. It can be written in Java. | |
| Pivot transformation source charset | The encoding of an external file defining the data transformation. | ISO-8859-1 (default) | any | |
| Deprecated | |||
| Error actions | Defines actions that should be performed when the specified transformation returns an Error code. See Return Values of Transformations. | ||
| Error log | URL of the file which error messages should be written to. These messages are generated during Error actions, see above. If the attribute is not set, messages are written to Console. | ||
Legend:
1): One of the Key or Group size attributes has to be always set.
2): These two values can either be given as an attribute or in your own transformation.
3): One of these attributes has to be set if you do not control the transformation by means of Field defining output field name and Field defining output field value.
Advanced Description
You can define the data transformation in two ways:
1) Set the Key or Group size attributes. See Group Data by Setting Attributes.
2) Write the transformation yourself in CTL/Java or provide it in an external file/Java class. See Define Your Own Transformation - Java/CTL.
Group Data by Setting Attributes
If you group data using the Key attribute your input should be sorted according to Key values. To tell the component how your input is sorted, specify Sort order. If the Key fields appear in the output metadata as well, Key values are copied automatically.
While when grouping with the Group size attribute, the component ignores the data itself and takes e.g. 3 records (for Group size = 3) and treats them as one group. Naturally, you have to have an adequate number of input records otherwise errors on reading will occur. The number has to be a multiple of Group size, e.g. 3, 6, 9 etc. for Group size = 3.
Then there are the two major attributes which describe the "mapping". They say:
which input field's value will designate the output field - Field defining output field name
which input field's value will be used as a value for that field Field defining output field value
As for the output metadata, it is arbitrary but fixed to field names. If your input data has extra fields, they are simply ignored (only fields defined as a value/name matter). Likewise output fields without any corresponding input records will be null.
Example 55.5. Data Transformation with Pivot - Using Key
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:
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]](figures/note.png) | Note |
|---|---|
If the input is not sorted (not like in the example), grouping records according to their count is especially handy. Omit Key and set Group size instead to read sequences of records that have exactly the number of records you need. |
Define Your Own Transformation - Java/CTL
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:
writing transformations in Denormalizer, the component Pivot is derived from: CTL Scripting Specifics and Java Interfaces for Denormalizer
Java
Compared to Denormalizer, the Pivot
component has new significant attributes: nameField and valueField.
These can be defined either as attributes (see above)
or by methods.
If the transformation is not defined,
the component uses com.opensys.cloudconnect.component.pivot.DataRecordPivotTransform
which copies values from valueField to nameField.
In Java, you can implement your own PivotTransform that overrides
DataRecordPivotTransform. However, you can
override only one method, e.g. getOutputFieldValue,
getOutputFieldIndex or others from PivotTransform
(that extends RecordDenormalize).
CTL
In CTL1/2, too,
you can set one of the attributes and implement
the other one with a method.
So you can e.g. set valueField
and implement getOutputFieldIndex. Or you can
set nameField and implement getOutputFieldValue.
In the compiled mode, the getOutputFieldValue and
getOutputFieldValueonError methods cannot be overridden.
When the transformation is written in CTL, the default append
and transform methods are always performed
before the user defined ones.
For a better understanding, examine the methods' documentation directly in the Transform editor.