Links

Transpose

Transposing a dataset means converting rows of data into columns or vice versa.

Rows to Columns

When your data is received by row per parameter for all assets; this Agent converts the rows to columns so that a row per asset (with multiple parameters as columns) is pushed downstream.
In this example, the data is first grouped by Date and SensorClass. Then the SensorType row data becomes the transposed column names and the Value row data becomes the transposed column values. The static columns appear in the output, unchanged.

Columns to Rows

When your data is received by row for all assets and parameters; this Agent converts the columns to rows such that each row in the input payload is split into a row per asset and transposed parameter column. The output consists of the following:
  • The (unchanged) static column(s)
  • The transposed column's header as a value under a new column name
  • The transposed column's value under a new value name
  • Null values for all other columns to be transposed, under their respective new value name
In this example, there is a row per timestamp and a column per asset name/parameter. We want to transpose this to a row per asset with columns for the asset name, parameter Tag1 and parameter Tag2.
On the agent, a grid is populated with an instruction per column to transpose. We specify that for column SC1_Tag1, the column name is the value of a new column Crusher and the column value is the value of a new column Tag1Column. Likewise for the other asset/parameter columns.
The result is a row per asset and transposed column, with null values for the other transposed columns.
The Calculated Field Transformation is handy to remove the "_Tag" of the asset name; and the Group & Merge Transformation to collapse the null values, i.e. one row per asset.
Details for an example and its configuration can be found in the How to Use section.

Pre-requisites

None at this time

Download

Name
Link
Transpose Transformation v1.07
Download
Please contact XMPro if you're looking for an older version of this Agent.
Last modified 2mo ago