Create a new column indicating the membership of another column in a given set.

set_indicator(
  source,
  rescol,
  testcol,
  testvalues,
  ...,
  translate_quotes = FALSE,
  env = parent.frame()
)

Arguments

source

source to select from.

rescol

name of column to land indicator in.

testcol

name of column to check.

testvalues

values to check for.

...

force later arguments to bind by name

translate_quotes

logical if TRUE translate quotes to SQL choice (simple replacement, no escaping).

env

environment to look to.

Value

set_indicator node.

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) { my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") d <- rq_copy_to(my_db, 'd', data.frame(a = c("1", "2", "1", "3"), b = c("1", "1", "3", "2"), q = 1, stringsAsFactors = FALSE), temporary = TRUE, overwrite = TRUE) # example set <- c("1", "2") op_tree <- d %.>% set_indicator(., "one_two", "a", set) %.>% set_indicator(., "z", "a", c()) print(column_names(op_tree)) print(columns_used(op_tree)) cat(format(op_tree)) sql <- to_sql(op_tree, my_db) cat(sql) print(DBI::dbGetQuery(my_db, sql)) op_tree2 <- d %.>% set_indicator(., "one_two", "a", set) %.>% set_indicator(., "z", "b", c()) %.>% select_columns(., c("z", "one_two")) print(column_names(op_tree2)) print(columns_used(op_tree2)) # cleanup DBI::dbDisconnect(my_db) }
#> [1] "a" "b" "q" "one_two" "z" #> $d #> [1] "a" "b" "q" #> #> mk_td("d", c( #> "a", #> "b", #> "q")) %.>% #> set_indicator(., one_two = a IN set) %.>% #> set_indicator(., z = a IN c()) #> SELECT *, 0 AS `z` FROM ( #> SELECT *, `a` IN ( '1' , '2' ) AS `one_two` FROM ( #> SELECT #> `a`, #> `b`, #> `q` #> FROM #> `d` #> ) tsql_62843073138380603018_0000000000 #> ) tsql_62843073138380603018_0000000001 #> a b q one_two z #> 1 1 1 1 1 0 #> 2 2 1 1 1 0 #> 3 1 3 1 1 0 #> 4 3 2 1 0 0 #> [1] "z" "one_two" #> $d #> [1] "a" "b" #>