Transpose - Data360_Analyze - 3 - 3.12

Data360 Analyze Server Help

Product type
Software
Portfolio
Verify
Product family
Data360
Product
Data360 Analyze
Version
3.12
Language
English
Product name
Data360 Analyze
Title
Data360 Analyze Server Help
Copyright
2023
First publish date
2016

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.

Tip: When selecting the field on which to transpose the data, note that if the field that you have selected contains more than 256 rows, you will need to increase the maximum number of allowed fields by typing a new value in the MaxFieldCount property. In general, it is recommended that you avoid selecting a field that has a very large number of rows as this could cause the node to run out of memory.

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:

  1. Run the Create Data node and connect it to a Transpose node.
  2. Select the Transpose node and in the TransposeField property select "Region".
  3. 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