Crunch Expressions, i.e. CrunchExpr and CrunchLogicalExpr, encapsulate derivations of Crunch variables, possibly composed of other functions which are only evaluated when sent to the server when creating a variable using VarDef() or using as.vector() to get data. The crunch database functions can be found in the Help Center, and can be called directly via crunchdbFunc()m but many have also been wrapped in native R functions, and are described in the details section below.

crunchdbFunc(fun, x, ...)

Arguments

fun

The name of the crunch database function to call

x

An input, a crunch variable, expression or R object

...

Other arguments passed to the database function

Details

Logical expressions

  • These logical operators ==, !=, &, |, !,%in% work the same way as their base R counterparts

  • is.selected(x) return CrunchLogicalExpr whether a value is in a selected category

  • rowAny(x) and rowAll(x) work row-wise on MultipleResponse Variables (and expressions), though na.rm is not implemented for all(x). %ornm% is similar to |, but where "not selected" beats "missing" (so FALSE %ornm% NA is FALSE instead of NA as it would be with FALSE | NA)

Comparisons

  • Comparison operators <, <=, >, >= work the same way as their base R counterparts.

  • crunchBetween(x, lower, upper, inclusive) to provide lower and upper bounds in a single expression.

Missing data expressions

  • is.na(x), is.valid(x) return CrunchLogicalExpr whether a single variable (or expression that creates one) is missing (or not missing).

  • rowAnyNA(x), rowAllNA(x) return CrunchLogicalExpr whether any/all values in an array variable (or expression that creates one) are missing.

  • complete.cases(x) returns an expression that is "selected" if all cases are non-missing, "missing" if they are all missing, and "other" otherwise.

Selection expressions

  • selectCategories(x, selections, collapse = TRUE) takes a categorical variable (or array) and marks categories as selected. selections should be a list of category names or values. If collapse is TRUE, (the default), it collapses the categories to "selected", "other" and "missing", but it is FALSE, then the old categories are preserved.

  • asSelected(x) returns an expression that condenses a categorical into 3 categories ("selected", "other" or "missing")

  • selectedDepth(x) returns an expression that creates a numeric variable that counts the number of selections across rows of an array variable (or expression that creates one)

  • arraySelections(x) returns an expression that takes an array and creates an array with each variable condensed to "selected", "other" or "missing" and an extra subvariable "any" that indicates whether any is selected.

  • alterCategoriesExpr(x, categories = NULL, category_order = NULL, subvariables = NULL) Change the category names, order, or subvariable names of categorical or Array variables (can only modify existing ones, not add or remove categories or subvariables). categories is a Categories object or a list of lists, each with a name indicating the new name, as well as an id or old_name to identify which category to modify. category_order is either a numeric vector indicating category ids or a character vector indicating the names of the categories in the order they should be displayed (note that all categories must be specified). subvariables is a list of lists, each with a name to rename the subvariable and an alias, old_nam or id to identify the subvariable. When x is an expression, all categories and subvariables must be identified by id.

Array expressions

  • makeFrame(x, numeric = NULL) an expression that creates an array from existing variables or expressions, see deriveArray() for more details

  • arraySubsetExpr(x, subvars, subvar_id = c("alias", "name", "id")) Take a subset of an existing array variable, identifying the subvariables by alias, name, or id (if x is an expression, you must use id).

  •  alterArrayExpr(
       x,
       add = NULL,
       order = NULL,
       order_id = c("alias", "name", "id"),
       remove = NULL,
       remove_id = c("alias", "name", "id"),
       subreferences = NULL,
       subreferences_id = c("alias", "name", "id")
    )
    

    Add, reorder, remove or rename subvariables on an an array variable x. The add argument is a list of variables or expressions, optionally named with the id they should have. order and remove are vectors of aliases, names or ids (specify which with order_id/remove_id). The subreferences object is a list of lists that are named the alias, name, or id (again specify which with subreferences_id) with metadata information like name and alias in the list.

Miscellaneous expressions

  • caseExpr(..., cases) Create a categorical variable from a set of logical expressions that when met are assigned to a category. See makeCaseVariable() for more details.

  • bin(x) returns a column's values binned into equidistant bins.

  • nchar(x) returns a numeric value indicating the length of a string (or missing reason) in a TextVariable (or expression that creates one)

  • unmissing(x) for a NumericVariable (or expression that creates one) return the values of the data, ignoring the ones set to missing.

  • trim(x, min, max) for a NumericVariable (or expression that creates one) return values that where all values less than min have been replaced with min and all values greater than max have been

  • crunchDifftime(e1, e2, resolution) Gets the difference between two datetimes as a number with specified resolution units (one of c("Y", "Q", "M", "W", "D", "h", "m", "s", "ms")).

  • datetimeFromCols(year, month, day, hours, minutes, seconds) create a Datetime variable from numeric variables or expressions (year, month, and day are required, but hours, minutes, and seconds are optional)

  • rollup(x, resolution) sets the resolution of a datetime variable or expression, see resolution()