Unpivot Data can be used to transform data from a short/wide format to a tall/skinny format.
Tip: You can replace a data table with a transformed version of itself.
To add an Unpivot Data transformation when adding a data table:
Select File > Add Data Tables... and add the data of interest.
Click Show transformations.
Select Unpivot from the drop-down list and click Add....
Tip: The Show transformations-section is also available when replacing data tables and when adding columns or rows to a data table.
To add a Pivot Data transformation to data that is already loaded into Spotfire:
Select Insert > Transformations....
In the Data table drop-down list, select the data table you want to add an unpivot transformation to.
Select Unpivot from the drop-down list and click Add....
Option |
Description |
Available
columns |
The columns available for use in the unpivot operation. Click a column name in the list to select it. To select more than one column, press Ctrl and click the column names in the list. Use one of the Add > buttons to send the selected column to either the Columns to transform or Columns to pass through field, see below. You can narrow down the list of available columns by typing a part of a name in the "Type to search" field. You can also enter an expression in the field, using the rules described on the Searching in TIBCO Spotfire page. |
Add
> |
Moves the selected columns from the Available columns field to the field next to the button. |
<
Remove |
Removes a column and brings it back to the Available columns field. |
Remove
All |
Removes all columns from the selected columns fields. |
Columns
to pass through |
The selected columns containing information that should be transferred to the unpivoted data set without any transformation. This could be ID columns, categorical information such as Region, Priority etc. |
Columns
to transform |
The selected columns containing the values that you wish to combine into a single column. The column names of these columns will be used as category values in the resulting new category column. Typically, this might be a number of columns containing the same type of data, such as sales figures for different years. |
Category
column name (contains transformed column names) |
Type a column name that summarizes the information provided in the columns that you have selected to transform. For instance, "Year" could be used when sales figures for several different years are to be combined to a single column. |
Data
type |
Allows you to set the data type of the category column name, if several are possible. |
Value
column name |
Type a column name that shows what type of information is included in the new value column. For instance, "Sales" would be a good name for a column containing sales figures for several different years. |
Data
type |
Allows you to set the data type of the value column name, if several are possible. |
Include
empty values |
Select this check box to transfer empty values to the unpivoted data view. If the check box is cleared, all records containing empty values will be discarded. |
Sample |
Gives you a preview of what the resulting data table will look like. Note: Uses the first 100 rows from the data table, so there might be some differences between the sample and the resulting data table. |
See also: