About Spectre Rotate

Spectre supports rotate operations that can take an input flow and rotate columns into multiple rows. The Spectre rotate block can be used wherever an input operation is allowed—for example, as a child to a build tag or cplan tag.

The output consists of:

  • Columns that are not rotated
  • A heading column that helps identify which columns were rotated to create the row
  • Values columns where the rotated column values end up
  • Rows for the rotated columns

The basic syntax for a rotate block is:

 rotate {
      rotate-output-names "heading-col-name" "rotated-data-col-1" "rotated-data-col-2"...
      row-out "row-value" "source-col-1" "source-col-2" 
      row-out "row-value" "source-col-1" "source-col-2" 
    }

Consider the following table with Oscar winners for Best Actor and Best Actress. You can rotate it into a table organized by award.

Each row contains distinct items. One column describes all the items on the row (Year). On each row, Actor First and Actor Last represent the Best Actor winner, while Actress First and Actress Last represent the Best Actress winner.

Each row describes both an actor and an actress, each of which has an Award. Note that you can tell who's who based on which column they are in. Each has a First Name and a Last Name.

You can rotate the data in a cPlan as follows:

dive {
	cplan {
		text-input "oscar-winners.txt"
		rotate {
			rotate-output-names "Award" "First Name" "Last Name"
			// The first argument because each row contains information about
			// several entities that are scattered across columns, each "Award"
			// can be inferred based on the column under which it appears.
			// The other arguments because each such entity has a "First Name" and
			// a "Last Name"
			row-out "Best Actor" "Actor First" "Actor Last"
			// The row's "Best Actor" entity's "First Name" appears in "Actor First"
			// The row's "Best Actor" entity's "Last Name" appears in "Actor Last"
			row-out "Best Actress" "Actress First" "Actress Last"
			// The row's "Best Actress" entity's "First Name" appears in "Actress First"
			// The row's "Best Actress" entity's "Last Name" appears in "Actress Last"
		}
	}
	window {
		dimension "Award"
		dimension "First Name"
		dimension "Last Name" 
		dimension "Year" format="YYYY"
	}
}

The result of the cPlan and dive are as follows:

The Year column is not part of the rotate, but it is carried along. The Year column is in the output and its values are duplicated for each entity from that row. For n entities per row (2), and r rows (4), there will be n x r rows in the output (8, in this example). The columns are displayed in the order they are listed in the window and sorted by the Award column, due to it being the first column in the output.

If the same rotate is used before a build, the cBase contains the following:

In the following example, the column order is automatically generated:

dive {
	cplan {
		text-input "oscar-winners.txt"
		rotate {
			rotate-output-names "Award" "First Name" "Last Name"
			// The first argument because each row contains information about
			// several entities that are scattered across columns, each "Award"
			// can be inferred based on the column under which it appears.
			// The other arguments because each such entity has a "First Name" and
			// a "Last Name"
			row-out "Best Actor" "Actor First" "Actor Last"
			// The row's "Best Actor" entity's "First Name" appears in "Actor First"
			// The row's "Best Actor" entity's "Last Name" appears in "Actor Last"
			row-out "Best Actress" "Actress First" "Actress Last"
			// The row's "Best Actress" entity's "First Name" appears in "Actress First"
			// The row's "Best Actress" entity's "Last Name" appears in "Actress Last"
		}
	}
	detail-window {
		all-cbase-columns
	}
}

The result of the cPlan and dive are as follows:

Any columns not referenced by the rotate operation appear as the left-most columns. In this example, that is the Year column. Next, are the columns that are part of the rotate operation, listed in the order they are referenced in the rotate operation. In this example, those columns are Award, followed by First Name and Last Name. The output is sorted by Year, due to it being the first column in the output.

If the same rotate is used before a build, the cBase contains the following: