Pivot

For details on creating a pivot, see this video:

A pivot converts a source table with a tall, skinny structure of few columns and many rows, such as lab data or ODM, into a target table that represents the same data in a more horizontal (short, fat) structure with more columns and fewer rows, based on the value in a pivot column, which must be associated with a codelist.

Tip:

For pivots to work correctly the source table must have a primary key that includes the pivot column plus the minimum number of columns required to ensure that each record is unique.

If there are more columns in the key than that, the resulting table may have too many rows with target column values sparsely populated among them rather than having fewer rows with a value in each column.

1. Select the source and target tables and click the Map icon, then select a Transformation Type of Pivot.
2. Click the Pivot icon.
3. Query for and select the pivot column. It must be associated with a codelist.
4. Click OK.
5. Click the Map Column icon.
6. In the View drop-down, select Columns and then select Filter Value.
7. Scroll over to the Filter Value column. For each pivoted column in the target table, select the pivot column value to identify the row in the source table from which to get the value for the target column.
8. Save.

Pivots and InForm Repeating Itemsets: In InForm, the primary key includes an internal index column called itemsetindex. In DMW, this internal column must be removed and replaced with the pivot column, which is associated with a codelist. To do this, create an intermediate direct transformation to remove itemsetindex and any other unneeded internal columns and add the pivot column to the primary key. Use the resulting table as the source table for the pivot transformation.

Pivot Example: Lab results are shipped one per row, but the review data model requires one row containing all lab results for each patient at the same visit.

Table 4-1 Source table in pivot example

SubjID	Date	Visit	Test	Unit	Value
972	03262112	5	IG	mh/dl	853
972	03262112	5	Lith	null	neg
972	03262112	5	PTH	pg/mL	285
989	03312112	3	IG	mh/dl	824
989	03312112	3	Lith	null	pos
989	03312112	3	PTH	pg/mL	290

The Test column, which contains the lab test name in the source table, is the pivot column. It is associated with a codelist whose values are IG, Lith, and PTH. The source columns Unit and Value are also pivoted. The columns SubjID, Date, and Visit are not pivoted.

Table 4-2 Target Table in Pivot Example

SubjID	Date	Visit	IG	IG_Unit	Lith	Lith_Unit	PTH	PTH_Unit
972	03262112	5	853	mh/dl	neg	null	285	pg/mL
989	03312112	3	824	mh/dl	pos	null	290	pg/mL