Teradata R Package Function Reference | 17.00 - 17.00 - 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: If you omit the "num.rows" argument, then you must specify this argument. 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
                                 )

Teradata R Package Function Reference | 17.00 - 17.00 - Pivot - Teradata R Package