Pivot transformations are used to aggregate data and also to change what constitutes a row and/or a column. This may be necessary if you need to change the granularity of a table. The first example will use a table of production data with one record per well per day. It will be transformed into a table with one record per well, and the gas and oil columns add up all production for each well. In the second example, with the same starting table, the data will be summed up for each production month. Each month is represented as a separate column of data.
Here are the steps for how to apply a pivot transformation.
Example 1 -- In this example we will transform the table from by well by day to by well and add up oil and gas production.
This is the starting data set
- Go to the Insert menu > select Transformations
- Select the appropriate data
table > select pivot from the drop down menu > click the Add button
- In the dialog shown, here is how the selections should be made
- Row identifiers -- this selection defines the row. In this example, we want one record per API.
- Column titles -- this selection specifies how data should be split up into columns. In this example, the columns are staying the same, so no selection is made
- Values and aggregation methods -- this selection specifies which columns should be aggregated and how. In this example, we want to add up oil and gas for each API.
- Column naming patterns -- this selection specifies how the columns should be named. Even though it looks and functions like a drop down, the user may also use this selector like a text box. Please the cursor in the text box and delete any unnecessary naming. In this example, I have chosen to show only the Value, which is Gas Prod and Oil Prod. The preview pane will update and show the end result.
- Transfer columns and aggregation methods -- this selection is where the user should specify any other columns of data they would like in the data table. For categorical columns, use the Unique Concatenate method.
- Transfer column naming patter -- same as column naming pattern.
Here is the end result -- one record per API with all production summed in the GasProd and OilProd columns.
Example 2 -- In the second example, with the same starting table, the desired end result is a table that shows one record per well, but splits up the production in a separate column for each production month.
Here are the selections. Scroll to the top of this post to see the starting table and note that a calculated column (c.Production Month) was created to break out the production month.
Here is the end result.