Skip to main content
waffle.svg
Domo Knowledge Base

ETL 2.0 Tiles: Pivot (Beta)

Version 2

 

Intro

There are two available tiles in the Pivot tab of ETL DataFlows. These include:

  • Collapse Columns
  • Uncollapse Columns 

For information about creating an ETL DataFlow, see Creating an ETL DataFlow.

For information about the Data Center, see Data Center Layout.

Pivot Tab.png

Getting this feature

If you are interested in joining the Beta for this feature, please contact your Customer Success Manager (CSM).

Collapse Columns Tile

Collapse Columns.png

The Collapse Columns tile lets you "unpivot" or "normalize" data in tables, transforming multiple columns in a single row into a single column with multiple rows. This action is useful only for unpivoting data in which the number of columns stays the same. If you add another column of data to the original file, this action does not collapse or unpivot the new column.

Example

For example, the Collapse Columns tile converts data in this pivoted format...

 

... to data in this format...

... using this configuration:

To configure the Collapse Columns tile,

  1. Click the Collapse Columns tile in the canvas.

  2. (Optional) Rename the tile by clicking , then entering the name you want.

  3. Enter the name of the column you want to create to contain the column headings from the columns to collapse.
    For example, "Product". 

  4. Enter the name of the column you want to create to contain the row values from the columns to be collapsed.
    For example, "Sales". 

  5. For each column you want to collapse, do the following:

    1. Select a column to normalize.
      For example, "Product A". 

    2. Enter a value representing the column header to be normalized.
      The value appears in the row of the new column. For example, "A".

    3. (Conditional) If you want to collapse another column, then click Add Column.

Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

Uncollapse Columns Tile

 Uncollapse Columns.png

The Uncollapse Columns tile lets you "pivot" or de-normalize data in tables using key-value pairs, transforming a single column with multiple rows into multiple columns in a single row.

Note: If you create a table that reaches the 1500 column limit, you will receive an error. You must reduce the number of columns to continue running your Magic ETL DataFlow. 

Example

For example, the Uncollapse Columns tile converts data in this format...

... to data in this format...

... using this configuration:

To configure the Uncollapse Columns tile,

  1. Click the Uncollapse Columns tile in the canvas.

  2. (Optional) Rename the tile by clicking , then entering the name you want.

  3. Enter the name of the key column you want to uncollapse into new column headers.
    For example, "Product". 

  4. Select the column to use to group the row values of the new columns.
    For example, "Month".

  5. For each new column you want to create from the key column, do the following:

    1. Enter the name of the new column header.
      For example, "Product A". 

    2. Enter a value from the key column that represents the column header to be uncollapsed. 
      For example, "A".

    3. Select the value column to use to fill the row value of the new column.

    4. For example, "Sales". 

      Note: For each column you add to be uncollapsed, select the same value column.
    5. (Conditional) If you want to add another column, then click Add Column.

Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.