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:
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 |
Parent topic: Table transformation types