Teradata R Package Function Reference - 16.20 - Pivot - Teradata R Package

Teradata® R Package Function Reference

prodname
Teradata R Package
vrm_release
16.20
created_date
February 2020
category
Programming Reference
featnum
B700-4007-098K

Description

The Pivot function pivots data that is stored in rows into columns. It outputs a table whose columns are based on the individual values from an input table column. The schema of the output table depends on the arguments to the function. The columns in the output table appear in the order specified by the "data.order.column" argument.
The function handles missing or NULL values automatically.

Usage

  td_pivot_mle (
      data = NULL,
      partition.columns = NULL,
      target.columns = NULL,
      pivot.column = NULL,
      pivot.keys = NULL,
      numeric.pivotkey = FALSE,
      num.rows = NULL,
      data.sequence.column = NULL,
      data.partition.column = NULL,
      data.order.column = NULL
  )

Arguments

data

Required Argument.
Specifies the tbl object of class "tbl_teradata" containing the data to be pivoted.

data.partition.column

Required Argument.
Specifies the partition by columns for the input tbl object in the argument "data". Values to this argument can be provided as vector, if multiple columns are used for ordering.

data.order.column

Optional Argument.
Specifies the order of the columns for the input tbl object in the argument "data". Values to this argument can be provided as vector, if multiple columns are used for ordering.
The columns in the output tbl object appear in order specified by data.order.column.

partition.columns

Required Argument.
Specifies the same columns as the data.partition.columns clause in any order.

target.columns

Required Argument.
Specifies the names of the input columns that contain the values to pivot.

pivot.column

Optional Argument. Required if you omit the argument "num.rows".
Specifies the name of the column that contains the pivot keys. If the pivot column contains numeric values, then the function casts them to VARCHAR.
Note: If you specify pivot.column, then you must use the "data.order.column" argument to order the input data. Otherwise, the contents of the columns of the output tbl object may vary from run to run.

pivot.keys

Optional Argument. Required if you specify pivot.column.
If you specify the pivot.column argument, then this argument specifies the names of the pivot keys. Do not use this argument without the pivot.column argument.
If pivot.column contains a value that is not specified as a pivot.key, then the function ignores the row containing that value.

numeric.pivotkey

Optional Argument.
Indicates whether the pivot key values are numeric values.
Default Value: FALSE

num.rows

Optional Argument. Required if you omit pivot.column.
Specifies the maximum number of rows in any partition. If a partition has fewer than "num.rows" rows, then the function adds NULL values; if a partition has more than "num.rows" rows, then the function omits the extra rows.
Use num.rows when no column contains pivot key.
Note: If you specify num.rows, then you must use the "data.order.column" argument to order the input data. Otherwise, the contents of the columns of the output tbl object may vary from run to run.

data.sequence.column

Optional Argument.
Specifies the vector of column(s) that uniquely identifies each row of the input argument "data". The argument is used to ensure deterministic results for functions which produce results that vary from run to run.

Value

Function returns an object of class "td_pivot_mle" which is a named list containing Teradata tbl object.
Named list member can be referenced directly with the "$" operator using name: result.

Examples

    # Get the current context/connection
    con <- td_get_context()$connection
    
    # Load example data.
    loadExampleData("pivot_example", "pivot_input")
    
    # Create remote tibble objects.
    pivot_input <- tbl(con, "pivot_input")

    # This example specifies the pivot.column argument and with the pivot.keys argument, which specifies
    # the values from the pivot.column to use as pivot keys. Because pivot.keys does not include 'dewpoint', the
    # function ignores rows that include 'dewpoint'.
    td_pivot_out1 <- td_pivot_mle(data = pivot_input,
                             data.partition.column = c("sn","city","week"),
                             data.order.column = c("week","attribute"),
                             partition.columns = c("sn","city", "week"),
                             target.columns = c("value1"),
                             pivot.column = "attribute",
                             pivot.keys = c("temp","pressure")
                             )
    # Specify the num.rows argument instead of specifying the pivot.column argument.
    td_pivot_out2 <- td_pivot_mle(data = pivot_input,
                             data.partition.column = c("sn","city","week"),
                             data.order.column = c("week","attribute"),
                             partition.columns = c("sn","city", "week"),
                             target.columns = c("value1"),
                             num.rows = 3
                             )