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 at the left of 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.

• How to modify a Transpose operator's settings.

• How to copy a Transpose operator.

• How to remove 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.
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.

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. Instead, here's a look at the DevTools Console:

What You Need

To set up this use case, you need:

• 1 Data Table component

• 1 Initializer component

• 1 Data Workflow component

To set up your Data Workflow, you need:

• 1 Input operator

• 1 Transpose operator

• 2 Console operators

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.

Enter dtCompanies in the Label and Property Name fields.

3.

In the Data Table, enter the following:

4.

Click Save.

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. Place your Data Workflow below your Data Table component.

2.

Enter dwfTranspose in the Canvas Label Text and Property Name fields.

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 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. Place the Initializer above the Data Table.

2.

Enter initTranspose in the Property ID and Canvas Label Text fields.

3.

Select New Submission as the Trigger Type.

4.

In the Outputs table, enter the following:

5.

Click Save.

6.

Save your module.

Now you can test your Data Workflow. Preview your module in Express View and open 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:

Changing a Transpose Operator's Settings

You can revisit and make changes to this operator.

1.

Click the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator to open its Info Window.

4.

Make changes to the operator's settings as needed.

5.

Click Save.

6.

Save your module.

Copying a Transpose Operator

You can make a copy of your operator using familiar keyboard settings. You can copy and paste an exact copy of your operator, matching all settings.

1.

Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator you want to duplicate.

4.

On your keyboard, press Command + C (Mac OS) or Ctrl + C (Windows/Linux) to copy the operator.

5.

On your keyboard, press Command + V (Mac OS) or Ctrl + V (Windows/Linux) to paste the copied operator(s) to the Data Workflow canvas.

6.

Click Save.

7.

Save your module.

TIP  Did you know you can copy an operator and paste it into a different Data Workflow? You'll use the same steps outlined above.

Removing a Transpose Operator

Lastly, you can delete this operator from your Data Workflow canvas. You can also use these same steps to delete a connection between two operators.

1.

Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator you want to delete.

4.

On your keyboard, press Delete.

5.

Click Save.

6.

Save your module.

Lab

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