Tamr Documentation

Unpivot Option

Use Unpivot to change the shape of a table.

UNPIVOT reorganizes the structure of the table by taking related columns of data and moving them into different rows.
UNPIVOT follows these rules:

  • Creates the new columns that you define. Tamr creates primary keys for them. See Primary Key Management. If no value is defined for a column, then it is null.
  • Carries over all columns that are not mentioned in its definition. Specifically, unpivot carries over all columns except for the columns that you explicitly specify, and the columns created by the expressions. To keep some columns at the left side of the table, such as table keys, mention them explicitly when you define the unpivot.

Example

In the following example, the input datasets have attributes for the spend each quarter, Q1, Q2, Q3, and Q4. You can use the Unpivot option to reorganize this table and change these four attributes into two: one attribute for the Quarter in which the spend occurred, and another attribute for the amount of Spend.

Before unpivot, the customer was represented by one record in the dataset, and each quarter of spend was one column.Before unpivot, the customer was represented by one record in the dataset, and each quarter of spend was one column.

Before unpivot, the customer was represented by one record in the dataset, and each quarter of spend was one column.

In this example, we create an attribute named Quarter and populate it with the name of the Q1, Q2, Q3, and Q4 attributes. The Spend attribute is then populated by values from the corresponding quarters.

The result is that Quarter and Spend are filled with the information from the original dataset. The columns from which the data was extracted are emptied. Any other unified attributes are preserved for each record, such as fullname. The result of using the Unpivot option in this example is that the number of records in the dataset is four times larger than the number of records in each of the input datasets.

The customer's spend by quarter is now represented by four records, each with a different value for the Quarter attribute.The customer's spend by quarter is now represented by four records, each with a different value for the Quarter attribute.

The customer's spend by quarter is now represented by four records, each with a different value for the Quarter attribute.

Unpivoting Additional Attributes

When you need to unpivot multiple attributes together, you can add dependent columns. One common use case is when the variable attribute contains data that have different units. In this case, you can add the units column as a dependent column.

To add a dependent column:

  1. Choose add dependent column and select Unified Attributes from a dropdown menu.
  2. Drag and drop the dependent column from the list at the left to its corresponding attribute on the right. In our example, the attribute containing unit information (VSTEMP_U) is the dependent column.

Here is an example of how it works. To see it, choose the information icon:

Note: You can also include an UNPIVOT statement in a transformation script. See UNPIVOT.

Updated 3 months ago



Unpivot Option


Use Unpivot to change the shape of a table.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.