The purpose of an unpivot transformation is to take many columns and transform them into a single category column and a single value column.  Why would you want to do this?  Perhaps data is provided in a particular arrangement, such as a canned report with a set format, but visualizing it in Spotfire is easier if the data could be rearranged.  In Excel this problem is commonly solved with the transpose function. 

 

In the data set shown below, oil, gas and water are separated into three columns.  Note I have a column called GasProd, which is native to the table, and a column called c.Gas Prod (BOE) that is a calculated column. The goal is to transform the c.Gas Prod (BOE, OilProd and WaterProd into one column that specifies the product and another column with the amount. 

 

Screenshot1.png


 

To accomplish the goal, an unpivot transformation will be performed with the following steps:

 

  1. Go to the Insert menu > select Transformations
  2. Set the data table to the table of interest (NOTE: you may choose to create a duplicate of the original table to transform so that a copy of the original is preserved)
  3. Set the Transformations: drop down to Unpivot as shown below > click the Add button

Screenshot2.png


  1. Rather than starting with the Columns to pass through, begin with Columns to transform.  Columns to transform are the Oil Prod, c.Gas (BOE) and Water Prod columns that we want to transform.  Note the c.Gas (BOE) column is not present in the list.  This is because only native columns OR columns that have been added via a Transformation.  Therefore, at this point in the process, it is necessary to remove the c.Gas (BOE) column and add it back again via the Insert menu > Transformations > Calculated column. 

Screenshot3.png



 

With that step complete, the c.Gas Prod (BOE) column appears in the menu.

Screernshot4.png



 

  1. Move OilProd, WaterPord and c.GasProd (BOE) from left to right under Columns to transform.
  2. All other columns should be moved from left to right under Columns to pass through, unless they are not needed, and in which case they may be left as they are in the dialog. 
  3. Name the Category column and the Value column.  Make sure that the data types are appropriate.

Screenshot5.png



  1. Click OK and click OK. 

 

 

This screenshot shows the end result. The oil, gas and water columns have been transformed into two columns, one that specifies the product and the other the volume.  

 

Screenshot6.png