Common elements

Parameters

When creating a new variable, you'll use a variety of inputs to specify the parameters required to fully define a given method. Each parameter of the following types:

variable

Refers to a Redivis variable, often limited to a specific subset of types. When using variable parameters in a given transform, you'll be able to select variables from the source table, variables in tables referenced by joins, or new variables created upstream of the parameter.

literal

Refers to a string, integer, boolean, or floating point value. When using literal parameters, you'll be able to type any constant value (e.g, false, 1.0, 'test'), or use a value list to reference the same set of values anywhere in your project. If using a literal parameter alongside another parameter, e.g., in a filter comparison, you may have to match the type of literal with that parameter.

enum

Refers to a set of distinct values, usually of a homogenous type. For example, if a required parameter used a "timezone" enum with 3 options (PST, MST or EST), you would have to select one of the three options (PST) for the value of that parameter.

boolean

Refers to values true or false.

Null

In many places throughout the transform you will have an option in the system menu to set a cell value(s) to NULL. Conceptually this means that the cell is empty and contains no value. If you look at the Cells view of a table, you will see NULL values shown in a grey color to indicate that these cells are empty.

Note that this is different than if the cell contained a string with the characters NULL. In this case the cell does have contents and will be treated so.

You will see throughout the transform references to how nulls are included or excluded for steps (such as the Order step) and in calculating summary statistics.

Format elements for dates and time methods

Some new variable methods (PARSE... and FORMAT...) allow for manipulation of Date, Time, or DateTime variable types. To do so, you'll need to define how the data is formatted using format elements.

For example, to work with dates in a mm/dd/yy format (common in the U.S.; e.g. 03/22/89 ), we would specify the format string %m/%d/%y . For a DateTime displayed as Mon Oct 17 2016 17:32:56 , we would specify %a %b %d %Y %H:%M:%S

Note that different format elements are used for the Retype step (CAST method) which are detailed elsewhere.

Below is a complete list of format elements and descriptions:

Format element	Description
%A	The full weekday name.
%a	The abbreviated weekday name.
%B	The full month name.
%b or %h	The abbreviated month name.
%C	The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99).
%c	The date and time representation.
%D	The date in the format %m/%d/%y.
%d	The day of the month as a decimal number (01-31).
%e	The day of month as a decimal number (1-31); single digits are preceded by a space.
%F	The date in the format %Y-%m-%d.
%G	The ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.
%g	The ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.
%H	The hour (24-hour clock) as a decimal number (00-23).
%I	The hour (12-hour clock) as a decimal number (01-12).
%j	The day of the year as a decimal number (001-366).
%k	The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.
%l	The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.
%M	The minute as a decimal number (00-59).
%m	The month as a decimal number (01-12).
%n	A newline character.
%P	Either am or pm.
%p	Either AM or PM.
%R	The time in the format %H:%M.
%r	The 12-hour clock time using AM/PM notation.
%S	The second as a decimal number (00-60).
%s	The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence.
%T	The time in the format %H:%M:%S.
%t	A tab character.
%U	The week number of the year (Sunday as the first day of the week) as a decimal number (00-53).
%u	The weekday (Monday as the first day of the week) as a decimal number (1-7).
%V	The week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.
%W	The week number of the year (Monday as the first day of the week) as a decimal number (00-53).
%w	The weekday (Sunday as the first day of the week) as a decimal number (0-6).
%X	The time representation in HH:MM:SS format.
%x	The date representation in MM/DD/YY format.
%Y	The year with century as a decimal number.
%y	The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s.
%%	A single % character.
%E#S	Seconds with # digits of fractional precision.
%E*S	Seconds with full fractional precision (a literal '*').
%E4Y	Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year.

Accessor elements for JSON methods

Some new variable methods (JSON extract..., JSON scalar, etc) allow for accessing subsets of the data contained within JSON-formatted strings. To do so, you'll need to define how the data is accessed using a JSONPath element.

For example, to access the firstName attribute within a JSON-formatted string { "firstName": "John", "lastName": "Doe" }, you'd specify a JSONPath element $.firstName.

More information is available in BigQuery's JSONPath format documentation.