Transpose Operator

Overview

The Transpose operator transposes, or rotates, data from columns to rows in a table. Using this operator, you can also apply filters to the source data. This lets you isolate and group specific values in your output.

This operator works by adding two new columns to your data. The first new column is called Key, and it holds your previous column headers as values. The second new column is called Value, and it holds the values for each row previously held in the column you designate in the Transpose Keys setting. You can also set custom names for these columns using the Transpose Name and Transpose Value settings in the operator’s Info window.

Let’s say you have a column with the key Name. In that column, you have the values Josephine, Art, and Lenna. You can use a Transpose operator to create a new column called Key (or any word of your choice). This new column holds three values: Name, Name, and Name. The operator also creates a new column called Value (or any word of your choice). This new column holds three values: Josephine, Art, and Lenna. Here, you can see this data before and after going through the Transpose operator:

Another example of using this operator is if you have a Data Table holding the worth of multiple companies. The Transpose operator converts the headers in your Data Table into values. These new values have their own header for clearer grouping.

You’ll find the Transpose operator under the Table group in the menu on the Data Workflow canvas.

What You'll Learn

In this article, you'll learn:

• About the Transpose operator's settings.

• How to add and configure a Transpose operator.

About the Info Window

Here's what the Transpose operator looks like, along with its Info window:

And here's a breakdown of each setting in the Info window:

Setting	Description
Category	Grayed out and non-adjustable setting indicating the operation type.
Label	Sets the label for the operator. The label you enter here appears below the operator in the Data Workflow canvas. This field is optional, but you'll want to set a label if you're using more than one of the operator type. A label can help you identify your operators without having to open any info windows.
Do Not Sanitize Formula	When this checkbox is clear, the operator does its best to clean up values coming into or defined in the operator. For example, removing special characters. Some of those special characters include ', $, #, &amp. The operator also cleans up complex values. For example, logically breaking up strings of numbers and letters using underscores. A sample module ID can go from "5f6b4cdfabf886024d4de2d3" to "5f_6b_4cdfabf_886024d_4de_2d_3". Sanitizing improves performance. But, it can also lead to unexpected data behavior. If your Create Field isn't behaving as expected, try selecting this box. When selected, the operator uses your values and expressions as-is. This is best when working with complex values or values that should remain unchanged. By default, this setting is selected, which means the operator doesn't attempt to sanitize.
Preserve Argument Type	When selected, this setting ensures the argument data type is respected when the operator executes.
Transpose Keys	The keys (headers) of the columns you want to transpose into values that show as rows in your data.
Transpose Name	The new key (column header) where your transposed column headers are stored as values. This setting is optional. If left blank, your new key (column header) defaults to Key.
Transpose Value	The new key (column header) where the values previously held in your transposed columns are stored. This setting is optional. If left blank, your new key (column header) defaults to Value.
Include Condition	A formula that tells your operator which data to perform the operation on. This setting is optional, and you’ll only use it if you don’t want to transpose all data in a key (column). For example, say you’re transposing data in a column with the key Company, but you only want to transpose data for a certain company in that column. That formula looks like Company=”companyName” where companyName is the company you’re transposing.

Adding a Transpose Operator

To see how this operator works, let's start with a data table of companies. All columns have headers, which are your keys. The keys are Company, Country, Type, and Worth. Below each of these column headers, you have your values. In your use case, let's say you want to rearrange the data so that Company, Type, and Worth are no longer keys but values. You can use the Transpose operator to do this.

NOTE  These instructions assume you have a module open, saved, and with a title.

Here's how the completed use case looks in the Module Builder:

This use case works behind the scenes, so you won't see anything in Express View Express View is how your end-user views your application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View.. Instead, here's a look at the DevTools Console The DevTools Console helps you securely store, build, test, and deploy your software.:

Configure the Data Table Component

Let's start with a 4-column Data Table to hold your company details.

1.

Drag and drop a Data Table component onto your canvas.

2.

In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. and Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . fields, enter dtCompanies.

3.

In the data table, enter the following:

4.

Click Save & Close.

Configure the Data Workflow Component

In order for the Transpose operator to work, you must input the data stored in your table. To bring your data into the Data Workflow, use an Input operator. From there, use a Transpose operator to turn the source data into rows instead of columns. To complete your Data Workflow, add two Console operators. One connects to your data table, so you can see how the source data appears without being transposed. The other connects to your Transpose operator to see your final data.

1.

Drag and drop a Data Workflow component onto your canvas, placing it below your Data Table component.

2.

In the Canvas Label Text and Property Name fields, enter dwfTranspose .

Configure the Input Operator

This operator references your source data.

1.

Drag and drop an Input operator onto your Data Workflow canvas.

2.

Configure the Input operator's Info window as follows:

Configure the Transpose Operator

Now, you know that you want Company, Type, and Worth to be values instead of keys. So, put Company, Type, Worth in the Transpose Keys field. This means the key Country stays the same, but Company, Type and Worth become row values instead of keys (column names).

1.

Drag and drop a Transpose operator onto your Data Workflow canvas.

2.

Configure the Transpose operator's Info window as follows:

3.

Connect the output port (right) of the Input operator to the input port (left) of the Transpose operator.

Configure the First Console Operator

A Console operator allows you to see how your source data appears in the DevTools Console The DevTools Console helps you securely store, build, test, and deploy your software. before going through the Transpose operator. This console shows your key/value pairs in their original formatting.

1.

Drag and drop a Console operator onto your Data Workflow canvas.

2.

Configure the Console operator's Info window as follows:

3.

Connect the output port (right) of the Input operator to the input port (left) of the Before Console operator.

Configure the Second Console Operator

Adding another Console operator allows you to check the output of your Data Workflow in the DevTools Console. This ensures your Transpose operator is configured correctly.

1.

Drag and drop a Console operator onto your Data Workflow canvas.

2.

Configure the Console operator's Info window as follows:

3.

Connect the output port (right) of the Transpose operator to the input port (left) of the Transposed Console operator.

4.

Save the Data Workflow.

Configure the Initializer Component

Finally, to trigger the Data Workflow, add an Initializer component. Set this Initializer to start your Data Workflow every time you open your application.

1.

Drag and drop an  Initializer component onto your canvas, placing it above the Data Table component.

2.

In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. and Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . fields, enter initTranspose.

3.

Set the Trigger Type Deteremines how the component triggers. as New Submission The component fires when the page or called module loads without an existing submission ID. Dashboards and remote execute modules are great uses for this trigger..

4.

In the Outputs table, enter the following:

5.

Click Save & Close.

6.

Save your module.

Now you can test your Data Workflow by previewing your module in Express View Express View is how your end-user views your application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View., and then opening the DevTools console. In the Before Console arrays, you can see how the data looked originally:

And in the Transposed Console arrays, you can see the transposed data arrays, now displayed in rows:

Resources

• You can view this complete use case here: https://training.unqork.io/#/form/60f0562dae5a9d02e78977dc/edit.

• Modify the Transpose operator in the Data Workflow's configuration window.