General transform from arbitrary record shape to arbitrary record shape.

convert_records(
  table,
  incoming_shape = NULL,
  outgoing_shape = NULL,
  ...,
  keyColumns = NULL,
  columnsToCopy_in = NULL,
  checkNames = TRUE,
  checkKeys = FALSE,
  strict = FALSE,
  incoming_controlTableKeys = colnames(incoming_shape)[[1]],
  outgoing_controlTableKeys = colnames(outgoing_shape)[[1]],
  tmp_name_source = wrapr::mk_tmp_name_source("crec"),
  temporary = TRUE,
  allow_rqdatatable_in = FALSE,
  allow_rqdatatable_out = FALSE
)

Arguments

table	data.frame or relop.
incoming_shape	data.frame, definition of incoming record shape.
outgoing_shape	data.frame, defintion of outgoing record shape.
...	force later arguments to bind by name.
keyColumns	character vector of column defining incoming row groups
columnsToCopy_in	character array of incoming column names to copy.
checkNames	logical, if TRUE check names.
checkKeys	logical, if TRUE check columnsToCopy form row keys (not a requirement, unless you want to be able to invert the operation).
strict	logical, if TRUE check control table name forms.
incoming_controlTableKeys	character, which column names of the incoming control table are considered to be keys.
outgoing_controlTableKeys	character, which column names of the outgoing control table are considered to be keys.
tmp_name_source	a tempNameGenerator from cdata::mk_tmp_name_source()
temporary	logical, if TRUE use temporary tables
allow_rqdatatable_in	logical, if TRUE allow rqdatatable shortcutting on simple conversions.
allow_rqdatatable_out	logical, if TRUE allow rqdatatable shortcutting on simple conversions.

Value

processing pipeline or transformed table

Examples



incoming_shape <- qchar_frame(
  "row",  "col1", "col2", "col3" |
  "row1",   v11,     v12,  v13   |
  "row2",   v21,     v22,  v23   |
  "row3",   v31,     v32,  v33   )


outgoing_shape <- qchar_frame(
  "column", "row1", "row2", "row3" |
  "col1",      v11,  v21  ,  v31   |
  "col2",      v12,  v22  ,  v32   |
  "col3",      v13,  v23  ,  v33   )

data <- build_frame(
  'record_id', 'row',  'col1', 'col2', 'col3'  |
  1,           'row1',  1,      2,      3      |
  1,           'row2',  4,      5,      6      |
  1,           'row3',  7,      8,      9      |
  2,           'row1',  11,     12,     13     |
  2,           'row2',  14,     15,     16     |
  2,           'row3',  17,     18,     19     )

print(data)
#>   record_id  row col1 col2 col3
#> 1         1 row1    1    2    3
#> 2         1 row2    4    5    6
#> 3         1 row3    7    8    9
#> 4         2 row1   11   12   13
#> 5         2 row2   14   15   16
#> 6         2 row3   17   18   19

convert_records(
  data,
  keyColumns = 'record_id',
  incoming_shape = incoming_shape,
  outgoing_shape = outgoing_shape)
#>   record_id column row1 row2 row3
#> 1         1   col1    1    4    7
#> 2         1   col2    2    5    8
#> 3         1   col3    3    6    9
#> 4         2   col1   11   14   17
#> 5         2   col2   12   15   18
#> 6         2   col3   13   16   19

td <- rquery::local_td(data)

ops <- convert_records(
  td,
  keyColumns = 'record_id',
  incoming_shape = incoming_shape,
  outgoing_shape = outgoing_shape)

cat(format(ops))
#> mk_td("data", c(
#>   "record_id",
#>   "row",
#>   "col1",
#>   "col2",
#>   "col3")) %.>%
#>  non_sql_node(., CREATE TEMPORARY TABLE "OUT" AS  SELECT a."record_id" "record_id", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col1" ELSE NULL END ) "v11", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col2" ELSE NULL END ) "v12", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col3" ELSE NULL END ) "v13", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col1" ELSE NULL END ) "v21", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col2" ELSE NULL END ) "v22", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col3" ELSE NULL END ) "v23", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col1" ELSE NULL END ) "v31", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col2" ELSE NULL END ) "v32", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col3" ELSE NULL END ) "v33" FROM "IN" a GROUP BY a."record_id") %.>%
#>  non_sql_node(., CREATE TEMPORARY TABLE "OUT" AS  SELECT a."record_id", b."column", CASE  WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v11"  WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v12"  WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v13" ELSE NULL END AS "row1", CASE  WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v21"  WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v22"  WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v23" ELSE NULL END AS "row2", CASE  WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v31"  WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v32"  WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v33" ELSE NULL END AS "row3" FROM "IN" a CROSS JOIN "crec_63088935191991423144_0000000004" b )