Pivoting Data


Some data files may contain repeating values, such as sales figures for a series of years. When uploading a spreadsheet or delimited file with columns of repeating values, you can preview these columns and transform them into rows.

Procedure: How to Pivot Columns Into Rows

  1. On the WebFOCUS home page, click the Get Data button .

    The Get Data dialog box opens, as shown in the following image.

  2. Under Local Files, click your Delimited or Excel file type for upload. An Excel file can contain a single worksheet or multiple worksheets. A delimited file is an individual data source.

  3. In the Open dialog box for your operating system, which is shown in the following image, select the file that you want to upload, and then click Open.


    This opens a preview, as shown in the following image. In this example, we are showing a multi-worksheet Excel file. The previewed worksheet contains columns for each year from 1960 to 2012.

  4. To multi-select all the year columns, click inside the first year column, hold down the Shift key, and click inside the last year column.

  5. Click the menu button in any of the selected year columns, and click Multiple columns to rows, as shown in the following image.


    The repeating year columns now display as rows, as shown in the following image.

  6. Click the menu button in the Pivot Value column, click Rename, and type a new column title in the dialog box that reflects the numeric cell you are describing. For example: Amount.

  7. Click OK.

  8. Click the menu button in the Pivot key column, click Rename, and type a new column title in the dialog box that reflects the numeric cell you are describing. For example: Year.

  9. Click OK.

    The column titles are renamed, as shown in the following image.

    To revert your pivoting changes, click Remove Pivot.

     

Pivoting Columns with Data Headers

If the repeating columns have headers that are dates, the fields are recognized as date values when pivoted into rows, so you can report from the table using date functions.

An uploaded data file with selected repeating columns that have date headers is shown in the following image.

Procedure: How to Select Columns to Pivot Using the Context Menu

From the preview page, you can easily select columns to pivot using the context menu.

  1. Click a column context menu icon .

    The context menu opens, as shown in the following image.

  2. Hover over the Pivot option.
  3. Click the Starting Column field dropdown arrow.
  4. Select the starting column of your pivot column range.
  5. Click the Ending Column field dropdown arrow.
  6. Select the ending column of your pivot column range.
  7. Click OK.

    The context menu closes, and the sheet preview refreshes to show the resulting pivot value and key columns.