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

Description

The Unpivot (td_unpivot_mle) 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
  )

Arguments

`data`	Required Argument. Specifies the tbl object of class "tbl_teradata" containing the data to be pivoted.
`unpivot`	Required Argument. Specifies the names of the target columns, which are the input columns to unpivot (convert to rows).
`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). If you specify FALSE, then for each unpivoted column, the function outputs the values in a single VARCHAR column. 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
`attribute.column`	Optional Argument. Specifies the name of the unpivoted attribute column in the output tbl_teradata. Default Value: "attribute"
`value.column`	Optional Argument. Specifies the name of the unpivoted value column in the output tbl_teradata. Default Value: "value_col"
`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.
`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_unpivot_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("unpivot_example", "unpivot_input")
    
    # Create remote tibble objects.
    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
                                      )