Skip to main content
Documentation
Documentation
Workday User Guide
Last Updated: 2023-06-23
Example: Unpivot Stock Vesting Data in a Dataset

Example: Unpivot Stock Vesting Data in a Dataset

This example illustrates how to create an Unpivot stage in a dataset to transpose fields (columns) of data into rows of data.
You have a CSV file of stock vesting data for your workers. The workers' stock vests in 3 installments, with a different number of shares on each date. The file contains 1 row of data for each worker, and a separate field for each vesting date and the number of shares that vested on each date.
The CSV file contains these rows and fields:
Name
Vest Date 01
QTY Vest 01
Vest Date 02
QTY Vest 02
Vest Date 03
QTY Vest 03
Dominique
04/01/2020
80
07/01/2020
85
10/01/2020
92
Desmond
02/01/2021
70
05/03/2021
65
08/02/2021
79
Stacey
02/01/2021
90
05/03/2021
80
08/02/2021
90
You need to transpose the data so that you have:
  • A field that contains all vesting dates for each worker.
  • A field that contains the number of shares that vested on each date for each worker.
Create a table by uploading a CSV file using the data in this example. See Steps: Create a Table by File Upload.
Create a derived dataset from the table. See Steps: Create a Derived Dataset.
Security:
Prism Datasets: Manage
 domain in the Prism Analytics functional area.
  1. From the
    View Dataset Details
     report of the derived dataset, click
    Edit
    .
  2. Click
    Add Stage
    , and select
    Unpivot
    .
    First, we'll unpivot the 3 date input fields, Vest_Date_01, Vest_Date_02, and Vest_Date_03.
  3. Click the plus sign next to
    Output Values
     so you have a total of 3 pairs of prompts.
  4. Select these values in the
    Input Fields
     and
    Output Values
     prompts:
    Input Fields
    Output Values
    Vest_Date_01
    Vesting Date 01
    Vest_Date_02
    Vesting Date 02
    Vest_Date_03
    Vesting Date 03
  5. In
    Field Names from Input
    , enter Vesting Schedule.
  6. In
    Values from Output
    , enter Vesting Dates.
  7. Click
    Preview
     to see the results of the settings you've made so far.
    On the
    Output Fields
     tab, Workday displays the results of the changes you defined in the Unpivot stage. Workday:
    • Removes the input fields you selected.
    • Displays the 2 new fields using the field names you defined.
    • Displays the new rows created using data from the removed input fields. In this example, Workday displays 9 rows total.
  8. Click the plus sign in the upper right corner of the Unpivot stage panel. Clicking the plus sign creates another group of input fields that you can unpivot into rows.
    Next, we'll unpivot the 3 quantity input fields, QTY_Vest_01, QTY_Vest_02, and QTY_Vest_03.
  9. Click the plus sign next to
    Output Values
     so you have a total of 3 pairs of prompts.
  10. In the
    Input Fields
     and
    Output Values
     prompts, enter these values:
    Input Fields
    Output Values
    QTY_Vest_01
    Quantity Vesting 01
    QTY_Vest_02
    Quantity Vesting 02
    QTY_Vest_03
    Quantity Vesting 03
  11. In
    Field Names from Input
    , enter Quantity Schedule.
  12. In
    Values from Output
    , enter Number of Shares.
  13. Click
    Preview
     to see the results of the settings you've made so far.
    On the
    Output Fields
     tab, Workday displays the results of the changes you defined in the Unpivot stage.
  14. Click
    Done
    , and click
    Save
    .
The output of the Unpivot stage contains these rows and fields:
Name
Vesting Schedule
Vesting Dates
Quantity Schedule
Number of Shares
Dominique
Vesting Date 01
04/01/2020
QTY Vesting 01
80
Dominique
Vesting Date 02
07/01/2020
QTY Vesting 02
85
Dominique
Vesting Date 03
10/01/2020
QTY Vesting 03
92
Desmond
Vesting Date 01
02/01/2021
QTY Vesting 01
70
Desmond
Vesting Date 02
05/03/2021
QTY Vesting 02
65
Desmond
Vesting Date 03
08/02/2021
QTY Vesting 03
79
Stacey
Vesting Date 01
02/01/2021
QTY Vesting 01
90
Stacey
Vesting Date 02
05/03/2021
QTY Vesting 02
80
Stacey
Vesting Date 03
08/02/2021
QTY Vesting 03
90
(Optional) Add a Manage Fields stage after the Unpivot stage to hide the Quantity Schedule field. In this example, the Quantity Schedule field contains data that is redundant with the Vesting Schedule field.

This content is not part of the Workday Administrator Guide.