Unpivot

For details on creating an unpivot, see this video:

An unpivot converts a source table with a short, fat structure of many columns and few rows into a target table that represents the same data in a tall, skinny structure with more rows and fewer columns, based on the value in an unpivot column, which must be associated with a codelist. Unpivot transformations are used for tables where multiple columns collect the same data, such as the same assessment repeated in each section of a CRF.

To create an Unpivot transformation:

1. Select the source and target tables and click the Map icon, then select a Transformation Type of Unpivot.
2. Click the Unpivot icon.
3. Query for and select the unpivot column from the target table columns. It must be associated with a codelist.
4. Click OK.
5. Click the Map Column icon.
6. For each pivoted column, select the target column and all the source columns that will feed data to it, and click Map. When appropriate, you can copy a mapping with its filter value. See Step 8 for more information.

   You can use the following pseudo-expressions without quotes:

   • To populate a target column with codelist values, add the following pseudo-expression to the column mapping expression:

     $UNPIVOT$CODEVALUE
     

     Note:

     A column that uses the pseudo-expression $UNPIVOT$CODEVALUE cannot be used as a pivot column.

   • By default, rows with null values for all target columns are suppressed. To include them in the target table, add the following pseudo-expression to the unpivot column mapping expression:

     $UNPIVOT$INCLUDENULLS
     

7. In the Mapped Sources tab, Filter Value field, for each pivoted column in the source table, select the pivot column value to identify the row in the target table into which to put the source column value.
8. If you have multiple target columns that map to the same source columns and have the same filter value, you can map one of them, then select it and click the Copy Map To icon. Select the target column and click OK.
9. Save.

Unpivot example: Multiple observations are collected in an InForm CRF using sections rather than itemsets. A flat form is created with three sections, one section for each time point in that visit for a blood draw. In InForm this is one CRF instance and one record but the standard review data model in use requires that these be three separate records.

Metadata values, the section name in this case, should be inserted as data in the corresponding row for that section.

Certain values in the flat section—the subject ID, visit, and date—should repeat on each row. These are the nonpivoted columns and the source column must be mapped to the target column.

The Section (Sect) column in the source table is the pivot column. It is associated with a codelist containing the values 0hr, 1hr, and 2hr.

Multiple columns in the source table—for example Sect1_Test, Sect2_Test, and Sect3_Test, map to a single column in the target table: Test.

Table 4-3 Source table columns in unpivot example (short, fat table)

SubjID	Date	Visit	Sect	Sect1_Test	Sect1_Unit	Sect1_Value	Sect	Sect2_Test	Sect2_Unit	Sect2_ Value	Sect	Sect3_Test	Sect3_Unit	Sect3_Value
509	01082112	1	0hr	Hb	gl	8	1hr	Hb	gl	8	2hr	Hb	gl	9
598	02092112	1	0hr	Hb	gl	8	1hr	Hb	gl	9	2hr	Hb	gl	10
613	02112112	1	0hr	Hb	gl	9	1hr	Hb	gl	10	2hr	Hb	gl	10

The system generates a row in the target table for each value in the codelist per set of nonpivoted values (SubjID, Date, and Visit) and populates the Section column in the target table with the codelist values as shown in Table 4-4.

Table 4-4 Target table columns in unpivot example (tall, skinny table)

SubjID	Date	Visit	Section	Test	Unit	Value
509	01082112	1	0hr	Hb	gl	8
509	01082112	1	1hr	Hb	gl	8
509	01082112	1	2hr	Hb	gl	9
598	02092112	1	0hr	Hb	gl	8
598	02092112	1	1hr	Hb	gl	9
598	02092112	1	2hr	Hb	gl	9
613	02112112	1	0hr	Hb	gl	9
613	02112112	1	1hr	Hb	gl	10
613	02112112	1	2hr	Hb	gl	10