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

Teradata® R Package Function Reference
prodname
Teradata R Package
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B700-4007-090K

Description

The Unpivot function pivots data that is stored in columns into rows, which is the reverse of the function Pivot (td_pivot_mle).

Usage

  td_unpivot_mle (
    data = NULL,
    unpivot = NULL,
    input.types = FALSE,
    attribute.column = "attribute",
    value.column = "value_col",
    accumulate = NULL,
    data.sequence.column = NULL,
    data.order.column = NULL
  )

Arguments

data

Required Argument.
Specifies the tbl_teradata containing the data to be pivoted.

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)

unpivot

Required Argument.
Specifies the names of the unpivot columns, which are the input columns to unpivot (convert to rows).
Types: character OR vector of Strings (character)

input.types

Optional Argument.
Specifies whether the unpivoted value column, in the output tbl_teradata, has the same data type as its corresponding unpivot column (if possible).

  1. If you specify FALSE, then for each unpivoted column, the function outputs the values in a single VARCHAR column.

  2. If you specify TRUE, then instead of one column for all attribute values, the function outputs each unpivoted value column in a separate column.

    • If the unpivot column has a data type REAL, the unpivoted value column has the data type DOUBLE PRECISION.

    • If the unpivot column has a data type INTEGER, the unpivoted value column has the data type LONG.

    • If the unpivot column has any other data type, the unpivoted value column has the data type VARCHAR.

Default Value: FALSE
Types: logical

attribute.column

Optional Argument.
Specifies the name of the unpivoted attribute column in the output tbl_teradata.
Default Value: "attribute"
Types: character

value.column

Optional Argument.
Specifies the name of the unpivoted value column in the output tbl_teradata.
Default Value: "value_col"
Types: character

accumulate

Required Argument.
Specifies the names of input columns other than unpivot columns, to copy to the output tbl_teradata. You must specify these columns in the same order that they appear in the input tbl_teradata.
Columns in "accumulate" argument can not be used in "unpivot" argument.
Types: character OR vector of Strings (character)

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_unpivot_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("unpivot_example", "unpivot_input")

    # Create object(s) of class "tbl_teradata".
    unpivot_input <- tbl(con, "unpivot_input")

    # Example 1 : With "input.types" = FALSE there is only one value column and it has the
    # data type VARCHAR and using default value for the arguments "attribute.column" 
    # and "value.column".
    td_unpivot_out1 <- td_unpivot_mle(data = unpivot_input,
                                      unpivot = c("temp","pressure","dewpoint"),
                                      input.types = FALSE,
                                      accumulate = c("sn","city","week")
                                      )

    # Example 2 : With "input.types" = TRUE, the output tbl_teradata has a separate 
    # column prefixed by "value.column" for each value in the unpivot argument 
    # (target column). The value colunms are suffixed by the datatype.
    # Custom names are provided for "attribute.column" and "value.column" arguments.
    td_unpivot_out2 <- td_unpivot_mle(data = unpivot_input,
                                      unpivot = c("temp","pressure","dewpoint"),
                                      input.types = TRUE,
                                      attribute.column = "climate_attributes",
                                      value.column = "attributevalue",
                                      accumulate = c("sn","city","week")
                                      )

    # Example 3 : Use the output of the td_pivot_mle() function as an input to the
    # td_unpivot_mle() function.
    loadExampleData("pivot_example", "pivot_input")
    pivot_input <- tbl(con, "pivot_input")
    
    # Create a td_pivot_out object for all three keys in the pivot column.
    td_pivot_out <- 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","dewpoint")
                                 )

    # Use the tbl_teradata object td_pivot_out as input to the td_unpivot_mle() function.
    td_unpivot_out3 <- td_unpivot_mle(data = td_pivot_out$result,
                                      unpivot = c("value1_temp", "value1_pressure",
                                                  "value1_dewpoint"),
                                      accumulate = c("sn","city","week"),
                                      input.types = FALSE
                                      )