Transposes (rotates) input data, converting data in a given input field into field names in the output metadata, and other unselected fields into values in the output records.
To configure this node, in the TransposeField property select the field that you want to rotate. The rows from the selected field will become the column headers in the output.
Consider whether the transposed field values will be valid as field names if you plan to subsequently publish the data to an external system or use other programming languages (e.g. R) to process the data.
Example
You have the following data in a Create Data node:
|
Sales total int |
Average products per customer int |
Region string |
|---|---|---|
| 800 | 20 | North West |
| 1000 | 30 | South East |
| 65 | 22 | North East |
| 50 | 15 | Wales |
You can rotate the data to show the regions along the header row, as follows:
- Run the Create Data node and connect it to a Transpose node.
- Select the Transpose node and in the TransposeField property select "Region".
- Run the Transpose node.
The following table shows the output of the Transpose node:
| OriginalFieldName | North West | South East | North East | Wales |
|---|---|---|---|---|
| Sales total | 800 | 1000 | 65 | 50 |
| Average products per customer | 20 | 30 | 22 |
15 |
Properties
TransposeField
Select the field on which to transpose the data. The values of each of the records for this field will be transformed to field names.
This should generally be a string or Unicode field as the values within this field will be turned into field names — though int, boolean, double and date types are also supported.
For Unicode fields, if the value of the field contains characters which cannot be mapped into a valid field name based on the Data360 Analyze server's character set, the node will error.
A value is required for this property.
RepeatedTransposeFieldBehavior
Optionally specify how to handle duplicate values in the selected TransposeField. Choose from:
- Rename Fields - Repeated values will be added to the output metadata with a generated name so as to avoid a conflicting field names. For each record, there will be a field in the output data for the TransposeField.
- Output New Records - New data records will be written for each of the duplicates. Fields will not be automatically renamed.
- Error - The node will error.
- Ignore - Records containing duplicate field values in the TransposeField will be ignored.
The default value is Rename Fields.
MissingFieldNameBehavior
Optionally specify how to handle repeating values in the TransposeField where only some of the values are repeated.
This property only has any effect if the RepeatedTransposeFieldBehavior property is set to Output New Records whereby additional records are produced for each set of duplicate TransposeField values. Choose from:
- Error - The node will fail.
- Log - A warning will be logged, and the node will continue processing - any missing fields will be Null on the output record.
- Ignore - The missing fields are ignored and will be Null on the output record.
The default value is Ignore.
MaxFieldCount
Optionally specify the maximum number of output fields allowed.
Incorrect configuration of the node (for example attempting to Transpose on a unique field like an account balance) can lead to very large output metadata.
To prevent inadvertent mistakes which would result in extremely large output records which could in turn cause the node to run out of memory, the default value of this property is 256.
NamesOutputField
Optionally specify the name of the output field which is to contain the field names transposed from the metadata in the input. The default property value is OriginalFieldName.
TransposedFieldPrefix
Optionally specify a prefix to use for the transposed field names.
The value of this property will be prepended with the value from the TransposeField to form the new field name in the output metadata. By default, no prefix is used.
Inputs and outputs
Inputs: in1
Outputs: Transposed Data