Use one column to pick values from other columns.

The pick column selects values from the columns it names (per-row).

lookup_by_column(
  source,
  pick,
  result,
  ...,
  tmp_name_source = wrapr::mk_tmp_name_source("qn"),
  temporary = TRUE,
  qualifiers = NULL,
  f_dt_factory = NULL
)

Arguments

source	source to select from (relop or data.frame).
pick	character scalar, name of column to control value choices.
result	character scalar, name of column to place values in.
...	force later arguments to be bound by name
tmp_name_source	wrapr::mk_tmp_name_source(), temporary name generator.
temporary	logical, if TRUE use temporary tables.
qualifiers	optional named ordered vector of strings carrying additional db hierarchy terms, such as schema.
f_dt_factory	optional signature f_dt_factory(pick, result) returns function with signature f_dt(d, nd) where d is a data.table. The point is the function must come from a data.table enabled package. Please see rqdatatable::make_dt_lookup_by_column for an example.

Examples

df = data.frame(x = c(1, 2, 3, 4),
                y = c(5, 6, 7, 8),
                choice = c("x", "y", "x", "z"),
                stringsAsFactors = FALSE)

# library("rqdatatable")
# df %.>%
#   lookup_by_column(., "choice", "derived")

if (requireNamespace("DBI", quietly = TRUE) &&
    requireNamespace("RSQLite", quietly = TRUE)) {
  db <- DBI::dbConnect(RSQLite::SQLite(),
                       ":memory:")
  RSQLite::initExtension(db)
  dr <- rq_copy_to(db, "dRemote", df,
                   overwrite = TRUE,
                   temporary = TRUE)

  ops <- dr %.>%
    lookup_by_column(., "choice", "derived")
  cat(format(ops))

  execute(db, ops) %.>%
     print(.)

  DBI::dbDisconnect(db)
}
#> mk_td("dRemote", c(
#>   "x",
#>   "y",
#>   "choice")) %.>%
#>  non_sql_node(., lookup_by_column(.; choice, derived))
#>   derived x y choice
#> 1       1 1 5      x
#> 2       6 2 6      y
#> 3       3 3 7      x
#> 4      NA 4 8      z