Teradata Package for R Function Reference | 17.00 - Pivot - Teradata Package for R - Look here for syntax, methods and examples for the functions included in the Teradata Package for R.

Teradata® Package for R Function Reference
Product
Teradata Package for R
Release Number
17.00
Published
July 2021
Language
English (United States)
Last Update
2023-08-08
dita:id
B700-4007
NMT
no
Product Category
Teradata Vantage
Pivot

Description

The Pivot function pivots data that is stored in rows into columns. It outputs a tbl_teradata whose columns are based on the individual values from an input tbl_teradata column. The schema of the output tbl_teradata depends on the arguments to the function. The columns in the output tbl_teradata 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_teradata containing the data to be pivoted.

data.partition.column

Required Argument.
Specifies Partition By columns for "data".
Values to this argument can be provided as a vector, if multiple columns are used for partition.
Types: character OR vector of Strings (character)

data.order.column

Optional Argument.
Specifies Order By columns for "data".
Values to this argument can be provided as a vector, if multiple columns are used for ordering.
Types: character OR vector of Strings (character)

partition.columns

Required Argument.
Specifies the same columns as the "data.partition.columns" argument (in any order).
Types: character OR vector of Strings (character)

target.columns

Required Argument.
Specifies the names of the input columns that contain the values to pivot.
Types: character OR vector of Strings (character)

pivot.column

Optional Argument.
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:

  1. If you omit the "num.rows" argument, then you must specify this argument.

  2. If you specify the "pivot.column" argument, then you must use the "data.order.column" argument to order the input data. Otherwise, the contents of the columns of the output tbl_teradata may vary from run to run.


Types: character

pivot.keys

Optional Argument. Required if you specify "pivot.column".
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.
Types: character OR vector of characters

numeric.pivotkey

Optional Argument.
Specifies a flag indicating whether the pivot key values are numeric values.
Default Value: FALSE
Types: logical

num.rows

Optional Argument.
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.
If you omit this argument, then you must specify the "pivot.column" argument.
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_teradata may vary from run to run.
Types: integer

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.
Types: character OR vector of Strings (character)

Value

Function returns an object of class "td_pivot_mle" which is a named list containing object of class "tbl_teradata".
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 object(s) of class "tbl_teradata".
    pivot_input <- tbl(con, "pivot_input")

    # Example 1: 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")
                                 )

    # Example 2: 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
                                 )