Spotfire's Unpivot Transformation - Converting Columns Into Rows

When analyzing data - particularly spreadsheet files - we frequently face information that has been organized in columns when it would be a lot more useful if broken down in rows.

Take for example the spreadsheet shown in Figure 1 where each row represents a different store and columns B to I contain the annual sales figures from 1998 to 2005. This kind of organization is good to look at tabular form but poses some problems when creating graphical visualizations.

Figure 1 - Store Sales per Year

If we import the Store Sales per Year spreadsheet into Spotfire without any further transformation we obtain a very similar organization (Figure 2) but what we want is to break down each row into eight new ones: one row with the store sales for 1998, one row with the store sales for 1999, and so on; and then is when Spotfire's Unpivot Transformation comes handy!

Figure 2 - Data Loaded into Spotfire

Now using Insert -> Transformation a dialog will popup where we must select the appropriate table and transformation (Unpivot) and click on the Add button which will display the Unpivot Data dialog (Figure 3).

Figure 3 - Configured Unpivot Transformation

Any column that should remain unchanged must be added to the Columns to pass through list as Store ID in our example. For every column added to the Columns to transform list Spotfire will generate a different row that has the column's name and value stored in the Category and Value columns.

Coming back to the Store Sales per Year spreadsheet when we added the 1998 column to the Columns to transform list Spotfire created a row for each store with the value 1998 in the Category column and the corresponding sales figure in the Value column.

Usually it makes sense to give more meaningful names to the Category and Value columns which were renamed Year and Sales in Figure 3.

After confirming everything is configured as desired we click OK a couple of times to close all dialogs and execute the transformation giving the result depicted in Figure 4.

Figure 4 - Transformed Table

And that's all for today's tips and tricks, I hope you find it useful.