You can use transpose transformation to transpose existing Columns to Rows. The Transpose can be applied based on the value in one or more attributes in the connected transform.

To work with Transpose transform in data flow, follow below steps.

Note:

Selecting the Transpose transform to be added to data flow

In the data flow canvas move to Data Flow pane and navigate to Transforms menu. Here, you can either select or drag and drop the Transpose transform to canvas. Now, link the required transform to Transpose Transform

The Transpose transformation is available only for Splice Machine, Snowflake, Azure Synapse, and BigQuery Data Flow.

Configuring Transpose transform 

General Tab: Provide the basic details for the Transpose transform.

1. The Name field auto populates the transform name and its editable.

2. In the Description text-box, provide a description and is optional.

3. By default the Persist Data checkbox is enabled and its uneditable. Here, Diyotta creates temporary tables during execution for the transformation in the data flow and the temporary table created will be dropped once the data flow executes successfully.

Properties tab: 

As per your requirement, in properties tab, select the type of Transpose, you can either choose ColumnsToRows or Collapse.

Option I: ColumnsToRows

If you wish to transform column fields to rows, select the transposing type as ColumnsToRows. 

Derived Attributes tab: You can add attributes in the transpose transform which will be the attributes from the connected transform. After selecting the transposing type as ColumnsToRows, by default few additional fields (Name and Value) are generated in Attributes tab.

The default fields Name and Value are non editable and Transpose checkbox is not available for these two fields.

  • To add/select new attributes, click Select.
  • Then Select Link Attributes wizard appears and displays the list of carry forward attributes available in the previous connected source/transform. Choose required attributes and click OK.

Note: The carry forward attributes already available in transform are Italicized.
  • The added attributes get listed in the Attributes tab and each attribute have Transpose checkbox for enabling. You are allowed to enable at least one or maximum of five attributes as Transpose fields. After execution, the selected attribute name(column name) is interchanged and displayed under the Name field as row and the respective attribute values are moved under Values field.

Note: 

    • The fields in the Attributes tab are specific to the data object type associated with the transform. For more information about these fields, refer Working with Data Object and select the specific database data object same as native processing platform.
    • The attributes selected as Transpose are not displayed in the Target instance transform.
    • Following operations are allowed on the attributes: Select, Cut, Copy, Paste, Up, Down, and Delete.
    • From the list of attributes, multiple attributes can be selected and we can perform/apply these operations.

Option II: Collapse

If you wish you to concatenate multiple rows data into a single row, select the transposing type as Collapse. By default, the Delimiter field displays comma(,) separator and if required you can also define other separator types.

Derived Attributes tab: You can add attributes in the transpose transform which will be the attributes from the connected transform.

Below are different options to add new attributes under Attributes tab.

1. Adding attributes from connected transforms

When there are no attributes present, then click on Click Here to add new attribute.

Then Select Link Attributes wizard appears and displays the list of carry forward attributes available in the previous connected source/transform. Choose required attributes and click OK.

Note: The carry forward attributes already available in transform are Italicized.

The added attributes get listed in the Derived Attributes tab and each attribute have Key and Collapse checkboxes. By default for each attribute you are allowed to enable either Key or Collapse option and you are allowed to select multiple Keys and only one Collapse field. The field selected as Key acts as the Column name and the field selected as Collapse will suppress the data into one field separated by comma.

Note:

    • The fields in the Attributes tab are specific to the data object type associated with the transform. For more information about these fields, refer Working with Data Object and select the specific database data object same as native processing platform.
    • The attributes selected as Key and Columns are only displayed in the Target instance transform.
    • Following operations are allowed on the attributes: Select, Cut, Copy, Paste, Up, Down, and Delete.
    • From the list of attributes, multiple attributes can be selected and we can perform/apply these operations.

2. Pasting attributes copied from another transform

You can paste attributes copied from another transform here.

When there are no attributes present, then click on the drop down on Click Here to see the paste option. Click on the Paste to paste the copied attributes.

The copied attributes are pasted as shown below:

When there are attributes already present, then click on Paste icon.

Script tab: The script tab allows you to view the run time script of the transform. The script is generated based on the condition specified in the transform.

  • To view the generated script, from the below pane, navigate to Script tab.