transformations:unpivot
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
transformations:unpivot [2016/06/19 20:02] – dmitry | transformations:unpivot [2021/07/19 02:16] (current) – [Examples] craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Unpivot | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Advanced\\ | ||
- | Converts a matrix table into a straight table by rotating columns into [Labels] and [Data] columns. This transformation | + | \\ |
+ | =====Description===== | ||
+ | Converts a matrix table into a straight table by rotating columns into "Labels" | ||
- | EXAMPLE | + | \\ |
+ | =====Use cases===== | ||
+ | This action is used to convert a cross-table (or matrix) into a straight table. It can be used, for instance, to convert a cross-table from an Excel spreadsheet into a table suitable for exporting into a database. | ||
- | **Table:** Winter Olympics medals | + | \\ |
+ | =====Action settings===== | ||
+ | ^Setting^Description^ | ||
+ | |Column name for labels|Enter a name for the column that will contain the column header labels from the matrix table. | ||
+ | |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. | ||
- | ^ Country | + | \\ |
- | | Canada | + | =====Remarks===== |
- | | United States | + | Typically, any columns used for row labels in the source matrix table will be selected under " |
- | | Norway | + | |
- | | Germany | + | |
- | **Transformation:** Unpivot | + | \\ |
+ | =====Examples===== | ||
+ | **Example:** Convert the matrix table, below, into a standard data table.\\ | ||
+ | \\ **Source table:** Winter Olympics medals | ||
+ | ^Country | ||
+ | | Canada | ||
+ | | United States | ||
+ | | Norway | ||
+ | | Germany | ||
+ | \\ | ||
+ | **Action parameters: | ||
+ | > Column name for labels is " | ||
+ | > Column name for data is " | ||
+ | > Unpivot | ||
+ | \\ | ||
+ | **Result table:** | ||
+ | ^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.\\ | ||
- | **Result:** | + | \\ |
+ | If no columns were selected under " | ||
- | ^ | + | **Result table:** |
- | | Canada | + | ^Labels |
- | | United States | + | |Country |
- | | Norway | + | |Country |
- | | Germany | + | |Country |
- | | Canada | + | |Country |
- | | United States | + | |winter 2010 | |
- | | Norway | + | |winter 2010 |
- | | Germany | + | |winter 2010 | 23| |
+ | |winter 2010 | 30| | ||
+ | |winter 2014 | 25| | ||
+ | |winter 2014 | 28| | ||
+ | |winter 2014 | 26| | ||
+ | |winter 2014 | 19| | ||
- | **See also** | + | \\ |
+ | ====Community examples==== | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | * [[https:// | ||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
* [[transformations: | * [[transformations: | ||
+ |
transformations/unpivot.txt · Last modified: 2021/07/19 02:16 by craigt