transformations:unpivot
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:unpivot [2015/05/01 12:40] – created elenap | transformations:unpivot [2021/04/24 17:20] – craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | This transformation converts a matrix table into a straight table by rotating columns into [Labels] and [Data] columns. | + | {{ transformations: |
+ | ====== UNPIVOT ====== | ||
+ | Category: Transform / Advanced\\ | ||
- | EXAMPLE | + | \\ |
+ | =====Description===== | ||
+ | Converts a matrix table into a straight table by rotating columns into " | ||
- | **Table:** Winter Olympics medals | + | \\ |
+ | =====Use cases===== | ||
+ | This action is used to convert a cross-tabular table into a normalized data table suited for database usage. | ||
- | ^ | + | \\ |
- | | Canada | + | =====Action settings===== |
- | | United States | + | ^Setting^Description^ |
- | | Norway | + | |Column name for labels|Enter a name for the column that will contain the column header labels from the matrix table. |
- | | Germany | + | |Column name for data|Enter a name for the column that will contain the data values from the matrix table. |
+ | |Unpivot all columns except these|Select fields that will not be unpivoted, but instead will be repeated down rows, as is, once for each column heading in the matrix table. | ||
- | **Transformation: | + | \\ |
+ | =====Remarks===== | ||
+ | Typically, any columns used for row labels in the source matrix table will be selected under " | ||
+ | \\ | ||
+ | =====Examples===== | ||
+ | **Objective: | ||
+ | \\ **Source table:** Winter Olympics medals | ||
+ | ^Country | ||
+ | | Canada | ||
+ | | United States | ||
+ | | Norway | ||
+ | | Germany | ||
+ | \\ | ||
+ | **Action parameters: | ||
+ | > Column name for labels is " | ||
+ | > Column name for data is " | ||
+ | > Unpivot all columns except these = " | ||
+ | \\ | ||
**Result:** | **Result:** | ||
+ | ^Country | ||
+ | | Canada | ||
+ | | United States | ||
+ | | Norway | ||
+ | | Germany | ||
+ | | Canada | ||
+ | | United States | ||
+ | | Norway | ||
+ | | Germany | ||
+ | Notice how the original set of Country values repeat for each value in Labels.\\ | ||
+ | |||
+ | \\ | ||
+ | If no columns were selected under " | ||
+ | |||
+ | **Result:** | ||
+ | ^Labels | ||
+ | |Country | ||
+ | |Country | ||
+ | |Country | ||
+ | |Country | ||
+ | |winter 2010 | 26| | ||
+ | |winter 2010 | 37| | ||
+ | |winter 2010 | 23| | ||
+ | |winter 2010 | 30| | ||
+ | |winter 2014 | 25| | ||
+ | |winter 2014 | 28| | ||
+ | |winter 2014 | 26| | ||
+ | |winter 2014 | 19| | ||
+ | |||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
+ | * [[transformations: | ||
- | ^ Country | ||
- | | Canada | ||
- | | United States | ||
- | | Norway | ||
- | | Germany | ||
- | | Canada | ||
- | | United States | ||
- | | Norway | ||
- | | Germany |
transformations/unpivot.txt · Last modified: 2021/07/19 02:16 by craigt