TRANSFORMER

DePivot

The Transformer DePivot conversion process object is used for rotating (“pivoting”) tables 90 degrees to the left.

If your source file is in table form, Transformer may not be able to extract the data properly, or creating a Transformer job may be extremely complex. A DePivot process gives you flexibility to reshape your data sets.

Use the DePivot process to create a file with a more suitable format. The column headers can then become values under a new column that is specified in the process together with other columns that contain the existing values. This is useful when it would be easier to manage the data in a table if it were laid out differently.

For example, we have received data from an employee survey in an Excel file.

Each question in the survey appears as a column heading with the corresponding responses in the column. Extracting data from this format would be complex, so we use a DePivot process to change the format so that the questions are presented in one column with the corresponding responses in the next column.

Creating a DePivot process

To create a DePivot process, right click in the Transformer design panel, choose Process -> DePivot. Connect it to the previous process as described in Processes.

Properties

General

Name - the name "DePivot conversion" is generated automatically but it may be changed
Description - an optional text describing the process

Audit

Modified by - the name of the last user to make changes to the object.
Last modified - the date and time when the object was last changed.
Created by - the name of the user who created the object.
Created - the date and time when the object was created.

Custom

Two new columns will be created by the DePivot process. Give the new columns descriptive names in Name of old header column and Name of new value column. E.g. we could use "Question" and "Response".

Name of old header column and Name of new value column may not be left blank.

The column headings from the source file are listed under Select columns that should not be depivoted in the DePivot conversion configuration. Select the columns that should NOT be depivoted, i.e. that should not be reformatted from heading row to column. Hold down Ctrl to select more than one.

Now, when you mark the DePivot process in the Transformer design panel and click the Preview icon , the Sample data tab will show the questions listed in the Question column and the corresponding responses in the Response column. Any columns that have not been reformatted will be presented on the left.

Running a Transformer job that contains a Depivot process in a different language than it was configured with may cause it to fail. The reason for this is that column names may be different in different languages.