Proposed Pull Request Change

title	titleSuffix	description	author	ms.author	ms.subservice	ms.custom	ms.topic	ms.date
Details and usage for all mapping data flow Functions	Azure Data Factory & Azure Synapse	Learn about details of usage and functionality for all expression functions in mapping data flows.	kromerm	makromer	data-flows	synapse	concept-article	07/25/2025

📄 Document Links

View on GitHub

View on Microsoft Learn

Raw New Markdown

Generating updated version of doc...

Rendered New Markdown

Generating updated version of doc...

+0 -0

--- title: Details and usage for all mapping data flow Functions titleSuffix: Azure Data Factory & Azure Synapse description: Learn about details of usage and functionality for all expression functions in mapping data flows. author: kromerm ms.author: makromer ms.subservice: data-flows ms.custom: synapse ms.topic: concept-article ms.date: 07/25/2025 --- # Data transformation expression usage in mapping data flows [!INCLUDE[appliesto-adf-asa-md](includes/appliesto-adf-asa-md.md)] [!INCLUDE[data-flow-preamble](includes/data-flow-preamble.md)] This article provides details about the use of all expressions and functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows. For summaries of each type of function supported, refer to the following articles: - [Aggregate functions](data-flow-aggregate-functions.md) - [Array functions](data-flow-array-functions.md) - [Cached lookup functions](data-flow-cached-lookup-functions.md) - [Conversion functions](data-flow-conversion-functions.md) - [Date and time functions](data-flow-date-time-functions.md) - [Expression functions](data-flow-expression-functions.md) - [Map functions](data-flow-map-functions.md) - [Metafunctions](data-flow-metafunctions.md) - [Window functions](data-flow-window-functions.md) ## Alphabetical list of all functions The following alphabetical list includes all the functions that are available in mapping data flows. ## A <a name="abs" ></a> ### <code>abs</code> <code>abs(<value1> : number) => number</code> Returns the absolute value of a number. * ``abs(-20) -> 20`` * ``abs(10) -> 10`` ___ <a name="acos" ></a> ### <code>acos</code> <code>acos(<value1> : number) => double</code> Calculates a cosine inverse value. * ``acos(1) -> 0.0`` ___ <a name="add" ></a> ### <code>add</code> <code>add(<value1> : any, <value2> : any) => any</code> Adds a pair of strings or numbers. Adds a date to many days. Adds a duration to a time stamp. Appends one array of a similar type to another. Same as the `+` operator. * ``add(10, 20) -> 30`` * ``10 + 20 -> 30`` * ``add('ice', 'cream') -> 'icecream'`` * ``'ice' + 'cream' + ' cone' -> 'icecream cone'`` * ``add(toDate('2012-12-12'), 3) -> toDate('2012-12-15')`` * ``toDate('2012-12-12') + 3 -> toDate('2012-12-15')`` * ``[10, 20] + [30, 40] -> [10, 20, 30, 40]`` * ``toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') + (days(1) + hours(2) - seconds(10)) -> toTimestamp('2019-02-04 07:19:18.871', 'yyyy-MM-dd HH:mm:ss.SSS')`` ___ <a name="addDays" ></a> ### <code>addDays</code> <code>addDays(<date/timestamp> : datetime, <days to add> : integral) => datetime</code> Adds days to a date or time stamp. Same as the `+` operator for dates. * ``addDays(toDate('2016-08-08'), 1) -> toDate('2016-08-09')`` ___ <a name="addMonths" ></a> ### <code>addMonths</code> <code>addMonths(<date/timestamp> : datetime, <months to add> : integral, [<value3> : string]) => datetime</code> Adds months to a date or time stamp. You can optionally pass a time zone. * ``addMonths(toDate('2016-08-31'), 1) -> toDate('2016-09-30')`` * ``addMonths(toTimestamp('2016-09-30 10:10:10'), -1) -> toTimestamp('2016-08-31 10:10:10')`` ___ <a name="and" ></a> ### <code>and</code> <code>and(<value1> : boolean, <value2> : boolean) => boolean</code> Uses the logical `AND` operator. Same as `&&`. * ``and(true, false) -> false`` * ``true && false -> false`` ___ <a name="approxDistinctCount" ></a> ### <code>approxDistinctCount</code> <code>approxDistinctCount(<value1> : any, [ <value2> : double ]) => long</code> Gets the approximate aggregate count of distinct values for a column. The optional second parameter is to control the estimation error. * ``approxDistinctCount(ProductID, .05) => long`` ___ <a name="array" ></a> ### <code>array</code> <code>array([<value1> : any], ...) => array</code> Creates an array of items. All items should be of the same type. If no items are specified, an empty string array is the default. Same as a `[]` creation operator. * ``array('Seattle', 'Washington')`` * ``['Seattle', 'Washington']`` * ``['Seattle', 'Washington'][1]`` * ``'Washington'`` ___ <a name="ascii" ></a> ### <code>ascii</code> <code>ascii(<Input> : string) => number</code> Returns the numeric value of the input character. If the input string has more than one character, the numeric value of the first character is returned. * ``ascii('A') -> 65`` * ``ascii('a') -> 97`` ___ <a name="asin" ></a> ### <code>asin</code> <code>asin(<value1> : number) => double</code> Calculates an inverse sine value. * ``asin(0) -> 0.0`` ___ <a name="assertErrorMessages" ></a> ### <code>assertErrorMessages</code> <code>assertErrorMessages() => map</code> Returns a map of all error messages for the row with assert ID as the key. Examples * ``assertErrorMessages() => ['assert1': 'This row failed on assert1.', 'assert2': 'This row failed on assert2.']. In this example, at(assertErrorMessages(), 'assert1') would return 'This row failed on assert1.'`` ___ <a name="associate" ></a> ### <code>associate</code> <code>reassociate(<value1> : map, <value2> : binaryFunction) => map</code> Creates a map of key/values. All the keys and values should be of the same type. If no items are specified, it defaults to a map of string to string type. Same as a ```[ -> ]``` creation operator. Keys and values should alternate with each other. * ``associate('fruit', 'apple', 'vegetable', 'carrot' )=> ['fruit' -> 'apple', 'vegetable' -> 'carrot']`` ___ <a name="at" ></a> ### <code>at</code> <code>at(<value1> : array/map, <value2> : integer/key type) => array</code> Finds the element at an array index. The index is 1 based. An out-of-bounds index results in a null value. Finds a value in a map when given a key. If the key isn't found, it returns a null value. * ``at(['apples', 'pears'], 1) => 'apples'`` * ``at(['fruit' -> 'apples', 'vegetable' -> 'carrot'], 'fruit') => 'apples'`` ___ <a name="atan" ></a> ### <code>atan</code> <code>atan(<value1> : number) => double</code> Calculates an inverse tangent value. * ``atan(0) -> 0.0`` ___ <a name="atan2" ></a> ### <code>atan2</code> <code>atan2(<value1> : number, <value2> : number) => double</code> Returns the angle in radians between the positive x-axis of a plane and the point given by the coordinates. * ``atan2(0, 0) -> 0.0`` ___ <a name="avg" ></a> ### <code>avg</code> <code>avg(<value1> : number) => number</code> Gets the average of values of a column. * ``avg(sales)`` ___ <a name="avgIf" ></a> ### <code>avgIf</code> <code>avgIf(<value1> : boolean, <value2> : number) => number</code> Gets the average of values of a column, based on criteria. * ``avgIf(region == 'West', sales)`` ___ ## B <a name="between" ></a> ### <code>between</code> <code>between(<value1> : any, <value2> : any, <value3> : any) => boolean</code> Checks if the first value is in between two other values inclusively. You can compare numeric, string, and `datetime` values. * ``between(10, 5, 24)`` * ``true`` * ``between(currentDate(), currentDate() + 10, currentDate() + 20)`` * ``false`` ___ <a name="bitwiseAnd" ></a> ### <code>bitwiseAnd</code> <code>bitwiseAnd(<value1> : integral, <value2> : integral) => integral</code> Uses the bitwise `And` operator across integral types. Same as the `&` operator. * ``bitwiseAnd(0xf4, 0xef)`` * ``0xe4`` * ``(0xf4 & 0xef)`` * ``0xe4`` ___ <a name="bitwiseOr" ></a> ### <code>bitwiseOr</code> <code>bitwiseOr(<value1> : integral, <value2> : integral) => integral</code> Uses the bitwise `Or` operator across integral types. Same as the `|` operator. * ``bitwiseOr(0xf4, 0xef)`` * ``0xff`` * ``(0xf4 | 0xef)`` * ``0xff`` ___ <a name="bitwiseXor" ></a> ### <code>bitwiseXor</code> <code>bitwiseXor(<value1> : any, <value2> : any) => any</code> Uses the bitwise `Or` operator across integral types. Same as the `|` operator * ``bitwiseXor(0xf4, 0xef)`` * ``0x1b`` * ``(0xf4 ^ 0xef)`` * ``0x1b`` * ``(true ^ false)`` * ``true`` * ``(true ^ true)`` * ``false`` ___ <a name="blake2b" ></a> ### <code>blake2b</code> <code>blake2b(<value1> : integer, <value2> : any, ...) => string</code> Calculates the Blake2 digest of a set of columns of varying primitive data types when given a bit length, which can only be multiples of 8 between 8 and 512. You can use it to calculate a fingerprint for a row. * ``blake2b(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4'))`` * ``'c9521a5080d8da30dffb430c50ce253c345cc4c4effc315dab2162dac974711d'`` ___ <a name="blake2bBinary" ></a> ### <code>blake2bBinary</code> <code>blake2bBinary(<value1> : integer, <value2> : any, ...) => binary</code> Calculates the Blake2 digest of a set of columns of varying primitive datatypes when given a bit length, which can only be multiples of 8 between 8 and 512. You can use it to calculate a fingerprint for a row. * ``blake2bBinary(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4'))`` * ``unHex('c9521a5080d8da30dffb430c50ce253c345cc4c4effc315dab2162dac974711d')`` ___ <a name="byItem" ></a> ### <code>byItem</code> <code>byItem(<parent column> : any, <column name> : string) => any</code> Finds a sub item within a structure or array of structure. If there are multiple matches, the first match is returned. If there are no matches, a `NULL` value is returned. The returned value must be type converted by one of the type conversion actions (such as `? date` and `? string`). Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``byItem( byName('customer'), 'orderItems') ? (itemName as string, itemQty as integer)`` * ``byItem( byItem( byName('customer'), 'orderItems'), 'itemName') ? string`` ___ <a name="byName" ></a> ### <code>byName</code> <code>byName(<column name> : string, [<stream name> : string]) => any</code> Selects a column value by name in the stream. You can pass an optional stream name as the second argument. If there are multiple matches, the first match is returned. If there are no matches, a `NULL` value is returned. The returned value must be type converted by one of the type conversion functions (such as `TO_DATE` and `TO_STRING`). Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``toString(byName('parent'))`` * ``toLong(byName('income'))`` * ``toBoolean(byName('foster'))`` * ``toLong(byName($debtCol))`` * ``toString(byName('Bogus Column'))`` * ``toString(byName('Bogus Column', 'DeriveStream'))`` ___ <a name="byNames" ></a> ### <code>byNames</code> <code>byNames(<column names> : array, [<stream name> : string]) => any</code> Select an array of columns by name in the stream. You can pass an optional stream name as the second argument. If there are multiple matches, the first match is returned. If there are no matches for a column, the entire output is a `NULL` value. The returned value requires a type conversion function (such as `toDate` and `toString`). Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``toString(byNames(['parent', 'child']))`` * ``byNames(['parent']) ? string`` * ``toLong(byNames(['income']))`` * ``byNames(['income']) ? long`` * ``toBoolean(byNames(['foster']))`` * ``toLong(byNames($debtCols))`` * ``toString(byNames(['a Column']))`` * ``toString(byNames(['a Column'], 'DeriveStream'))`` * ``byNames(['orderItem']) ? (itemName as string, itemQty as integer)`` ___ <a name="byOrigin" ></a> ### <code>byOrigin</code> <code>byOrigin(<column name> : string, [<origin stream name> : string]) => any</code> Selects a column value by name in the origin stream. The second argument is the origin stream name. If there are multiple matches, the first match is returned. If there are no matches, a `NULL` value is returned. The returned value must be type converted by one of the type conversion functions (such as `TO_DATE` and `TO_STRING`). Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``toString(byOrigin('ancestor', 'ancestorStream'))`` ___ <a name="byOrigins" ></a> ### <code>byOrigins</code> <code>byOrigins(<column names> : array, [<origin stream name> : string]) => any</code> Selects an array of columns by name in the stream. The second argument is the stream from where it originated. If there are multiple matches, the first match is returned. If there are no matches, a `NULL` value is returned. The returned value must be type converted by one of the type conversion functions (such as `TO_DATE` and `TO_STRING`). Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``toString(byOrigins(['ancestor1', 'ancestor2'], 'ancestorStream'))`` ___ <a name="byPath" ></a> ### <code>byPath</code> <code>byPath(<value1> : string, [<streamName> : string]) => any</code> Finds a hierarchical path by name in the stream. You can pass an optional stream name as the second argument. If no such path is found, it returns `NULL`. Column names/paths known at design time should be addressed only by using their name or dot notation path. Computed inputs aren't supported, but you can use parameter substitutions. * ``byPath('grandpa.parent.child') => column`` ___ <a name="byPosition" ></a> ### <code>byPosition</code> <code>byPosition(<position> : integer) => any</code> Selects a column value by its relative position (1 based) in the stream. If the position is out of bounds, it returns a `NULL` value. The returned value must be type converted by one of the type conversion functions (such as `TO_DATE` or `TO_STRING`). Computed inputs aren't supported, but you can use parameter substitutions. * ``toString(byPosition(1))`` * ``toDecimal(byPosition(2), 10, 2)`` * ``toBoolean(byName(4))`` * ``toString(byName($colName))`` * ``toString(byPosition(1234))`` ___ ## C <a name="case" ></a> ### <code>case</code> <code>case(<condition> : boolean, <true_expression> : any, <false_expression> : any, ...) => any</code> Based on alternating conditions, the `case` function applies one value or the other. If the number of inputs is even, the other is defaulted to `NULL` for the last condition. * ``case(10 + 20 == 30, 'dumbo', 'gumbo') -> 'dumbo'`` * ``case(10 + 20 == 25, 'bojjus', 'do' < 'go', 'gunchus') -> 'gunchus'`` * ``isNull(case(10 + 20 == 25, 'bojjus', 'do' > 'go', 'gunchus')) -> true`` * ``case(10 + 20 == 25, 'bojjus', 'do' > 'go', 'gunchus', 'dumbo') -> 'dumbo'`` ___ <a name="cbrt" ></a> ### <code>cbrt</code> <code>cbrt(<value1> : number) => double</code> Calculates the cube root of a number. * ``cbrt(8) -> 2.0`` ___ <a name="ceil" ></a> ### <code>ceil</code> <code>ceil(<value1> : number) => number</code> Returns the smallest integer not smaller than the number. * ``ceil(-0.1) -> 0`` ___ <a name="char" ></a> ### <code>char</code> <code>char(<Input> : number) => string</code> Returns the ASCII character represented by the input number. If the number is greater than 256, the result is equivalent to char (number % 256). * ``char(65) -> 'A'`` * ``char(97) -> 'a'`` ___ <a name="coalesce" ></a> ### <code>coalesce</code> <code>coalesce(<value1> : any, ...) => any</code> Returns the first not null value from a set of inputs. All inputs should be of the same type. * ``coalesce(10, 20) -> 10`` * ``coalesce(toString(null), toString(null), 'dumbo', 'bo', 'go') -> 'dumbo'`` ___ <a name="collect" ></a> ### <code>collect</code> <code>collect(<value1> : any) => array</code> Collects all values of the expression in the aggregated group into an array. You can collect and transform structures to alternate structures during this process. The number of items is equal to the number of rows in that group and can contain null values. The number of collected items should be small. * ``collect(salesPerson)`` * ``collect(firstName + lastName))`` * ``collect(@(name = salesPerson, sales = salesAmount) )`` ___ <a name="collectUnique" ></a> ### <code>collectUnique</code> <code>collectUnique(<value1> : any) => array</code> Collects all values of the expression in the aggregated group into a unique array. You can collect and transform structures to alternate structures during this process. The number of items is equal to the number of rows in that group and can contain null values. The number of collected items should be small. * ``collect(salesPerson)`` * ``collect(firstName + lastName))`` * ``collect(@(name = salesPerson, sales = salesAmount) )`` ___ <a name="columnNames" ></a> ### <code>columnNames</code> <code>columnNames(<value1> : string, i><value1> : boolean) => array</code> Gets the names of all output columns for a stream. You can pass an optional stream name as the first argument. The second argument is also optional, with false as the default. If you set the second argument to ``true()``, Data Factory returns only columns that are drifted via schema drift. * ``columnNames()`` * ``columnNames('DeriveStream')`` * ``columnNames('DeriveStream', true())`` * ``columnNames('', true())`` ___ <a name="columns" ></a> ### <code>columns</code> <code>columns([<stream name> : string]) => any</code> Gets the values of all output columns for a stream. You can pass an optional stream name as the second argument. * ``columns()`` * ``columns('DeriveStream')`` ___ <a name="compare" ></a> ### <code>compare</code> <code>compare(<value1> : any, <value2> : any) => integer</code> Compares two values of the same type. Returns a negative integer if value1 < value2, 0 if value1 == value2, and a positive value if value1 > value2. * ``(compare(12, 24) < 1) -> true`` * ``(compare('dumbo', 'dum') > 0) -> true`` ___ <a name="concat" ></a> ### <code>concat</code> <code>concat(<this> : string, <that> : string, ...) => string</code> Concatenates a variable number of strings together. Same as the `+` operator with strings. * ``concat('dataflow', 'is', 'awesome') -> 'dataflowisawesome'`` * ``'dataflow' + 'is' + 'awesome' -> 'dataflowisawesome'`` * ``isNull('sql' + null) -> true`` ___ <a name="concatWS" ></a> ### <code>concatWS</code> <code>concatWS(<separator> : string, <this> : string, <that> : string, ...) => string</code> Concatenates a variable number of strings together with a separator. The first parameter is the separator. * ``concatWS(' ', 'dataflow', 'is', 'awesome') -> 'dataflow is awesome'`` * ``isNull(concatWS(null, 'dataflow', 'is', 'awesome')) -> true`` * ``concatWS(' is ', 'dataflow', 'awesome') -> 'dataflow is awesome'`` ___ <a name="contains" ></a> ### <code>contains</code> <code>contains(<value1> : array, <value2> : unaryfunction) => boolean</code> Returns `true` if any element in the provided array evaluates as `true` in the provided predicate. Th `contains` function expects a reference to one element in the predicate function as `#item`. * ``contains([1, 2, 3, 4], #item == 3) -> true`` * ``contains([1, 2, 3, 4], #item > 5) -> false`` ___ <a name="cos" ></a> ### <code>cos</code> <code>cos(<value1> : number) => double</code> Calculates a cosine value. * ``cos(10) -> -0.8390715290764524`` ___ <a name="cosh" ></a> ### <code>cosh</code> <code>cosh(<value1> : number) => double</code> Calculates a hyperbolic cosine of a value. * ``cosh(0) -> 1.0`` ___ <a name="count" ></a> ### <code>count</code> <code>count([<value1> : any]) => long</code> Gets the aggregate count of values. If one or more optional columns are specified, it ignores `NULL` values in the count. * ``count(custId)`` * ``count(custId, custName)`` * ``count()`` * ``count(iif(isNull(custId), 1, NULL))`` ___ <a name="countAll" ></a> ### <code>countAll</code> <code>countAll([<value1> : any]) => long</code> Gets the aggregate count of values including null values. * ``countAll(custId)`` * ``countAll()`` ___ <a name="countDistinct" ></a> ### <code>countDistinct</code> <code>countDistinct(<value1> : any, [<value2> : any], ...) => long</code> Gets the aggregate count of distinct values of a set of columns. * ``countDistinct(custId, custName)`` ___ <a name="countAllDistinct" ></a> ### <code>countAllDistinct</code> <code>countAllDistinct(<value1> : any, [<value2> : any], ...) => long</code> Gets the aggregate count of distinct values of a set of columns including null values. * ``countAllDistinct(custId, custName)`` ___ <a name="countIf" ></a> ### <code>countIf</code> <code>countIf(<value1> : boolean, [<value2> : any]) => long</code> Gets the aggregate count of values, based on criteria. If the optional column is specified, it ignores `NULL` values in the count. * ``countIf(state == 'CA' && commission < 10000, name)`` ___ <a name="covariancePopulation" ></a> ### <code>covariancePopulation</code> <code>covariancePopulation(<value1> : number, <value2> : number) => double</code> Gets the population covariance between two columns. * ``covariancePopulation(sales, profit)`` ___ <a name="covariancePopulationIf" ></a> ### <code>covariancePopulationIf</code> <code>covariancePopulationIf(<value1> : boolean, <value2> : number, <value3> : number) => double</code> Gets the population covariance of two columns, based on criteria. * ``covariancePopulationIf(region == 'West', sales)`` ___ <a name="covarianceSample" ></a> ### <code>covarianceSample</code> <code>covarianceSample(<value1> : number, <value2> : number) => double</code> Gets the sample covariance of two columns. * ``covarianceSample(sales, profit)`` ___ <a name="covarianceSampleIf" ></a> ### <code>covarianceSampleIf</code> <code>covarianceSampleIf(<value1> : boolean, <value2> : number, <value3> : number) => double</code> Gets the sample covariance of two columns, based on criteria. * ``covarianceSampleIf(region == 'West', sales, profit)`` ___ <a name="crc32" ></a> ### <code>crc32</code> <code>crc32(<value1> : any, ...) => long</code> Calculates the CRC32 hash of a set of columns of varying primitive data types when given a bit length, which can only be of values `0(256`), `224`, `256`, `384`, and `512`. You can use it to calculate a fingerprint for a row. * ``crc32(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> 3630253689L`` ___ <a name="cumeDist" ></a> ### <code>cumeDist</code> <code>cumeDist() => integer</code> The `cumeDist` function computes the position of a value relative to all values in the partition. The result is the number of rows preceding or equal to the current row in the ordering of the partition divided by the total number of rows in the window partition. Any tie values in the ordering evaluate to the same position. * ``cumeDist()`` ___ <a name="currentDate" ></a> ### <code>currentDate</code> <code>currentDate([<value1> : string]) => date</code> Gets the current date when this job starts to run. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone of the data factory's data center/region is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``currentDate() == toDate('2250-12-31') -> false`` * ``currentDate('PST') == toDate('2250-12-31') -> false`` * ``currentDate('America/New_York') == toDate('2250-12-31') -> false`` ___ <a name="currentTimestamp" ></a> ### <code>currentTimestamp</code> <code>currentTimestamp() => timestamp</code> Gets the current time stamp when the job starts to run with a local time zone. * ``currentTimestamp() == toTimestamp('2250-12-31 12:12:12') -> false`` ___ <a name="currentUTC" ></a> ### <code>currentUTC</code> <code>currentUTC([<value1> : string]) => timestamp</code> Gets the current time stamp as UTC. If you want your current time to be interpreted in a different time zone than your cluster time zone, you can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. It defaults to the current time zone. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. To convert the UTC time to a different time zone, use `fromUTC()`. * ``currentUTC() == toTimestamp('2050-12-12 19:18:12') -> false`` * ``currentUTC() != toTimestamp('2050-12-12 19:18:12') -> true`` * ``fromUTC(currentUTC(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true`` ___ ## D <a name="dayOfMonth" ></a> ### <code>dayOfMonth</code> <code>dayOfMonth(<value1> : datetime) => integer</code> Gets the day of the month when given a date. * ``dayOfMonth(toDate('2018-06-08')) -> 8`` ___ <a name="dayOfWeek" ></a> ### <code>dayOfWeek</code> <code>dayOfWeek(<value1> : datetime) => integer</code> Gets the day of the week when given a date. For example, `1` is Sunday, `2` is Monday, ... and `7` is Saturday. * ``dayOfWeek(toDate('2018-06-08')) -> 6`` ___ <a name="dayOfYear" ></a> ### <code>dayOfYear</code> <code>dayOfYear(<value1> : datetime) => integer</code> Gets the day of the year when given a date. * ``dayOfYear(toDate('2016-04-09')) -> 100`` ___ <a name="days" ></a> ### <code>days</code> <code>days(<value1> : integer) => long</code> Duration in milliseconds for the number of days. * ``days(2) -> 172800000L`` ___ <a name="decode" ></a> ### <code>decode</code> <code>decode(<Input> : any, <Charset> : string) => binary</code> Decodes the encoded input data into a string based on the given charset. You can use a second (optional) argument to specify which charset to use. Examples are `US-ASCII`, `ISO-8859-1`, `UTF-8` (default), `UTF-16BE`, `UTF-16LE`, and `UTF-16`. * ``decode(array(toByte(97),toByte(98),toByte(99)), 'US-ASCII') -> abc`` ___ <a name="degrees" ></a> ### <code>degrees</code> <code>degrees(<value1> : number) => double</code> Converts radians to degrees. * ``degrees(3.141592653589793) -> 180`` ___ <a name="denseRank" ></a> ### <code>denseRank</code> <code>denseRank() => integer</code> Computes the rank of a value in a group of values specified in a window's order by clause. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values don't produce gaps in the sequence. The `denseRank` function works even when data isn't sorted and looks for change in values. * ``denseRank()`` ___ <a name="distinct" ></a> ### <code>distinct</code> <code>distinct(<value1> : array) => array</code> Returns a distinct set of items from an array. * ``distinct([10, 20, 30, 10]) => [10, 20, 30]`` ___ <a name="divide" ></a> ### <code>divide</code> <code>divide(<value1> : any, <value2> : any) => any</code> Divides pair of numbers. Same as the `/` operator. * ``divide(20, 10) -> 2`` * ``20 / 10 -> 2`` ___ <a name="dropLeft" ></a> ### <code>dropLeft</code> <code>dropLeft(<value1> : string, <value2> : integer) => string</code> Removes as many characters from the left of the string. If the drop requested exceeds the length of the string, an empty string is returned. * ``dropLeft('bojjus', 2) => 'jjus'`` * ``dropLeft('cake', 10) => ''`` ___ <a name="dropRight" ></a> ### <code>dropRight</code> <code>dropRight(<value1> : string, <value2> : integer) => string</code> Removes as many characters from the right of the string. If the drop requested exceeds the length of the string, an empty string is returned. * ``dropRight('bojjus', 2) => 'bojj'`` * ``dropRight('cake', 10) => ''`` ___ ## E <a name="encode" ></a> ### <code>encode</code> <code>encode(<Input> : string, <Charset> : string) => binary</code> Encodes the input string data into binary based on a charset. A second (optional) argument can be used to specify which charset to use. Examples are `US-ASCII`, `ISO-8859-1`, `UTF-8` (default), `UTF-16BE`, `UTF-16LE`, and `UTF-16`. * ``encode('abc', 'US-ASCII') -> array(toByte(97),toByte(98),toByte(99))`` ___ <a name="endsWith" ></a> ### <code>endsWith</code> <code>endsWith(<string> : string, <substring to check> : string) => boolean</code> Checks if the string ends with the supplied string. * ``endsWith('dumbo', 'mbo') -> true`` ___ <a name="equals" ></a> ### <code>equals</code> <code>equals(<value1> : any, <value2> : any) => boolean</code> Uses the comparison equals operator. Same as the `==` operator. * ``equals(12, 24) -> false`` * ``12 == 24 -> false`` * ``'bad' == 'bad' -> true`` * ``isNull('good' == toString(null)) -> true`` * ``isNull(null == null) -> true`` ___ <a name="equalsIgnoreCase" ></a> ### <code>equalsIgnoreCase</code> <code>equalsIgnoreCase(<value1> : string, <value2> : string) => boolean</code> Uses the comparison equals operator ignoring case. Same as the `<=>` operator. * ``'abc'<=>'Abc' -> true`` * ``equalsIgnoreCase('abc', 'Abc') -> true`` ___ <a name="escape" ></a> ### <code>escape</code> <code>escape(<string_to_escape> : string, <format> : string) => string</code> Escapes a string according to a format. Literal values for acceptable format are `json`, `xml`, `ecmascript`, `html`, and `java`. ___ <a name="except" ></a> ### <code>except</code> <code>except(<value1> : array, <value2> : array) => array</code> Returns a difference set of one array from another dropping duplicates. * ``except([10, 20, 30], [20, 40]) => [10, 30]`` ___ <a name="expr" ></a> ### <code>expr</code> <code>expr(<expr> : string) => any</code> Results in an expression from a string, which is the same as writing this expression in a nonliteral form. You can use it to pass parameters as string representations. * ``expr('price * discount') => any`` ___ ## F <a name="factorial" ></a> ### <code>factorial</code> <code>factorial(<value1> : number) => long</code> Calculates the factorial of a number. * ``factorial(5) -> 120`` ___ <a name="false" ></a> ### <code>false</code> <code>false() => boolean</code> Always returns a false value. Use the function `syntax(false())` if a column is named `false`. * ``(10 + 20 > 30) -> false`` * ``(10 + 20 > 30) -> false()`` ___ <a name="filter" ></a> ### <code>filter</code> <code>filter(<value1> : array, <value2> : unaryfunction) => array</code> Filters elements out of the array that don't meet the provided predicate. Filter expects a reference to one element in the predicate function as `#item`. * ``filter([1, 2, 3, 4], #item > 2) -> [3, 4]`` * ``filter(['a', 'b', 'c', 'd'], #item == 'a' || #item == 'b') -> ['a', 'b']`` ___ <a name="find" ></a> ### <code>find</code> <code>find(<value1> : array, <value2> : unaryfunction) => any</code> Find the first item from an array that matches the condition. It takes a filter function where you can address the item in the array as `#item`. For deeply nested maps, you can refer to the parent maps by using the `#item_n` (#item_1, #item_2...) notation. * ``find([10, 20, 30], #item > 10) -> 20`` * ``find(['azure', 'data', 'factory'], length(#item) > 4) -> 'azure'`` * ``find([ @( name = 'Daniel', types = [ @(mood = 'jovial', behavior = 'terrific'), @(mood = 'grumpy', behavior = 'bad') ] ), @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] ) ], contains(#item.types, #item.mood=='happy') /*Filter out the happy kid*/ )`` * `` @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] ) `` ___ <a name="first" ></a> ### <code>first</code> <code>first(<value1> : any, [<value2> : boolean]) => any</code> Gets the first value of a column group. If the second parameter `ignoreNulls` is omitted, Data Factory assumes false. * ``first(sales)`` * ``first(sales, false)`` ___ <a name="flatten" ></a> ### <code>flatten</code> <code>flatten(<array> : array, <value2> : array ..., <value2> : boolean) => array</code> Flattens an array or arrays into a single array. Arrays of atomic items are returned unaltered. The last argument is optional and is defaulted to false to flatten recursively more than one level deep. * ``flatten([['bojjus', 'girl'], ['gunchus', 'boy']]) => ['bojjus', 'girl', 'gunchus', 'boy']`` * ``flatten([[['bojjus', 'gunchus']]] , true) => ['bojjus', 'gunchus']`` ___ <a name="floor" ></a> ### <code>floor</code> <code>floor(<value1> : number) => number</code> Returns the largest integer not greater than the number. * ``floor(-0.1) -> -1`` ___ <a name="fromBase64" ></a> ### <code>fromBase64</code> <code>fromBase64(<value1> : string, <encoding type> : string) => string</code> Decodes the specific base64-encoded string. You can optionally pass the encoding type. * ``fromBase64('Z3VuY2h1cw==') -> 'gunchus'`` * ``fromBase64('SGVsbG8gV29ybGQ=', 'Windows-1252') -> 'Hello World'`` ___ <a name="fromUTC" ></a> ### <code>fromUTC</code> <code>fromUTC(<value1> : timestamp, [<value2> : string]) => timestamp</code> Converts to the time stamp from UTC. You can optionally pass the time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. It defaults to the current time zone. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``fromUTC(currentTimestamp()) == toTimestamp('2050-12-12 19:18:12') -> false`` * ``fromUTC(currentTimestamp(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true`` ___ ## G <a name="greater" ></a> ### <code>greater</code> <code>greater(<value1> : any, <value2> : any) => boolean</code> Uses the comparison greater operator. Same as `>` operator. * ``greater(12, 24) -> false`` * ``('dumbo' > 'dum') -> true`` * ``(toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS') > toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> true`` ___ <a name="greaterOrEqual" ></a> ### <code>greaterOrEqual</code> <code>greaterOrEqual(<value1> : any, <value2> : any) => boolean</code> Uses the comparison greater than or equal to operator. Same as `>=` operator. * ``greaterOrEqual(12, 12) -> true`` * ``('dumbo' >= 'dum') -> true`` ___ <a name="greatest" ></a> ### <code>greatest</code> <code>greatest(<value1> : any, ...) => any</code> Returns the greatest value among the list of values as input, skipping null values. Returns `null` if all inputs are null. * ``greatest(10, 30, 15, 20) -> 30`` * ``greatest(10, toInteger(null), 20) -> 20`` * ``greatest(toDate('2010-12-12'), toDate('2011-12-12'), toDate('2000-12-12')) -> toDate('2011-12-12')`` * ``greatest(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS'), toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')) -> toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')`` ___ ## H <a name="hasColumn" ></a> ### <code>hasColumn</code> <code>hasColumn(<column name> : string, [<stream name> : string]) => boolean</code> Checks for a column value by name in the stream. You can pass an optional stream name as the second argument. Address column names known at design time by their names. Computed inputs aren't supported, but you can use parameter substitutions. * ``hasColumn('parent')`` ___ <a name="hasError" ></a> ### <code>hasError</code> <code>hasError([<value1> : string]) => boolean</code> Checks if the asset with a provided ID is marked as an error. Examples * ``hasError('assert1')`` * ``hasError('assert2')`` ___ <a name="hasPath" ></a> ### <code>hasPath</code> <code>hasPath(<value1> : string, [<streamName> : string]) => boolean</code> Checks if a certain hierarchical path exists by name in the stream. You can pass an optional stream name as the second argument. Column names/paths known at design time should be addressed only by using their name or dot notation path. Computed inputs aren't supported, but you can use parameter substitutions. * ``hasPath('grandpa.parent.child') => boolean`` ___ <a name="hex" ></a> ### <code>hex</code> <code>hex(\<value1\>: binary) => string</code> Returns a hex string representation of a binary value * ``hex(toBinary([toByte(0x1f), toByte(0xad), toByte(0xbe)])) -> '1fadbe'`` ___ <a name="hour" ></a> ### <code>hour</code> <code>hour(<value1> : timestamp, [<value2> : string]) => integer</code> Gets the hour value of a time stamp. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``hour(toTimestamp('2009-07-30 12:58:59')) -> 12`` * ``hour(toTimestamp('2009-07-30 12:58:59'), 'PST') -> 12`` ___ <a name="hours" ></a> ### <code>hours</code> <code>hours(<value1> : integer) => long</code> Gets the duration in milliseconds for the number of hours. * ``hours(2) -> 7200000L`` ___ ## I <a name="iif" ></a> ### <code>iif</code> <code>iif(<condition> : boolean, <true_expression> : any, [<false_expression> : any]) => any</code> Applies one value or the other based on a condition. If the other is unspecified, the value is considered `NULL`. Both the values must be compatible (such as numeric and string). * ``iif(10 + 20 == 30, 'dumbo', 'gumbo') -> 'dumbo'`` * ``iif(10 > 30, 'dumbo', 'gumbo') -> 'gumbo'`` * ``iif(month(toDate('2018-12-01')) == 12, 345.12, 102.67) -> 345.12`` ___ <a name="iifNull" ></a> ### <code>iifNull</code> <code>iifNull(<value1> : any, [<value2> : any], ...) => any</code> Returns the first not null item when given two or more inputs. This function is equivalent to the `coalesce` function. * ``iifNull(10, 20) -> 10`` * ``iifNull(null, 20, 40) -> 20`` * ``iifNull('azure', 'data', 'factory') -> 'azure'`` * ``iifNull(null, 'data', 'factory') -> 'data'`` ___ <a name="in" ></a> ### <code>in</code> <code>in(<array of items> : array, <item to find> : any) => boolean</code> Checks if an item is in the array. * ``in([10, 20, 30], 10) -> true`` * ``in(['good', 'kid'], 'bad') -> false`` ___ <a name="initCap" ></a> ### <code>initCap</code> <code>initCap(<value1> : string) => string</code> Converts the first letter of every word to uppercase. Words are identified as separated by whitespace. * ``initCap('cool iceCREAM') -> 'Cool Icecream'`` ___ <a name="instr" ></a> ### <code>instr</code> <code>instr(<string> : string, <substring to find> : string) => integer</code> Finds the position (1 based) of the substring within a string. If not found, `0` is returned. * ``instr('dumbo', 'mbo') -> 3`` * ``instr('microsoft', 'o') -> 5`` * ``instr('good', 'bad') -> 0`` ___ <a name="intersect" ></a> ### <code>intersect</code> <code>intersect(<value1> : array, <value2> : array) => array</code> Returns an intersection set of distinct items from two arrays. * ``intersect([10, 20, 30], [20, 40]) => [20]`` ___ <a name="isBitSet" ></a> ### <code>isBitSet</code> <code>isBitSet (\<value1\> : array, \<value2\>:integer ) => boolean</code> Checks if a bit position is set in this bitset. * ``isBitSet(toBitSet([10, 32, 98]), 10) => true`` ___ <a name="isBoolean" ></a> ### <code>isBoolean</code> <code>isBoolean(\<value1\>: string) => boolean</code> Checks if the string value is a Boolean value according to the rules of ``toBoolean()``. * ``isBoolean('true') -> true`` * ``isBoolean('no') -> true`` * ``isBoolean('microsoft') -> false`` ___ <a name="isByte" ></a> ### <code>isByte</code> <code>isByte(\<value1\> : string) => boolean</code> Checks if the string value is a byte value when given an optional format according to the rules of ``toByte()``. * ``isByte('123') -> true`` * ``isByte('chocolate') -> false`` ___ <a name="isDate" ></a> ### <code>isDate</code> <code>isDate (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the input date string is a date by using an optional input date format. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. If the input date format is omitted, the default format is ``yyyy-[M]M-[d]d``. Accepted formats are `:[ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ]`. * ``isDate('2012-8-18') -> true`` * ``isDate('12/18--234234' -> 'MM/dd/yyyy') -> false`` ___ <a name="isDecimal" ></a> ### <code>isDecimal</code> <code>isDecimal (\<value1\> : string) => boolean</code> Checks if the string value is a decimal value when given an optional format according to the rules of ``toDecimal()``. * ``isDecimal('123.45') -> true`` * ``isDecimal('12/12/2000') -> false`` ___ <a name="isDelete" ></a> ### <code>isDelete</code> <code>isDelete([<value1> : integer]) => boolean</code> Checks if the row should be deleted. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isDelete()`` * ``isDelete(1)`` ___ <a name="isDistinct" ></a> ### <code>isDistinct</code> <code>isDistinct(<value1> : any , <value1> : any) => boolean</code> Finds if a column or set of columns is distinct. It doesn't count null as a distinct value. * ``isDistinct(custId, custName) => boolean`` ___ <a name="isDouble" ></a> ### <code>isDouble</code> <code>isDouble (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the string value is a double value when given an optional format according to the rules of ``toDouble()``. * ``isDouble('123') -> true`` * ``isDouble('$123.45' -> '$###.00') -> true`` * ``isDouble('icecream') -> false`` ___ <a name="isError" ></a> ### <code>isError</code> <code>isError([<value1> : integer]) => boolean</code> Checks if the row is marked as an error. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2` and the default value is `1`. * ``isError()`` * ``isError(1)`` ___ <a name="isFloat" ></a> ### <code>isFloat</code> <code>isFloat (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the string value is a float value when given an optional format according to the rules of ``toFloat()``. * ``isFloat('123') -> true`` * ``isFloat('$123.45' -> '$###.00') -> true`` * ``isFloat('icecream') -> false`` ___ <a name="isIgnore" ></a> ### <code>isIgnore</code> <code>isIgnore([<value1> : integer]) => boolean</code> Checks if the row should be ignored. For transformations taking more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isIgnore()`` * ``isIgnore(1)`` ___ <a name="isInsert" ></a> ### <code>isInsert</code> <code>isInsert([<value1> : integer]) => boolean</code> Checks if the row is marked for insert. For transformations taking more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isInsert()`` * ``isInsert(1)`` ___ <a name="isInteger" ></a> ### <code>isInteger</code> <code>isInteger (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the string value is an integer value when given an optional format according to the rules of ``toInteger()``. * ``isInteger('123') -> true`` * ``isInteger('$123' -> '$###') -> true`` * ``isInteger('microsoft') -> false`` ___ <a name="isLong" ></a> ### <code>isLong</code> <code>isLong (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the string value is a long value when given an optional format according to the rules of ``toLong()``. * ``isLong('123') -> true`` * ``isLong('$123' -> '$###') -> true`` * ``isLong('gunchus') -> false`` ___ <a name="isMatch" ></a> ### <code>isMatch</code> <code>isMatch([<value1> : integer]) => boolean</code> Checks if the row is matched at lookup. For transformations taking more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isMatch()`` * ``isMatch(1)`` ___ <a name="isNan" ></a> ### <code>isNan</code> <code>isNan (\<value1\> : integral) => boolean</code> Checks if the value isn't a number. * ``isNan(10.2) => false`` ___ <a name="isNull" ></a> ### <code>isNull</code> <code>isNull(<value1> : any) => boolean</code> Checks if the value is `NULL`. * ``isNull(NULL()) -> true`` * ``isNull('') -> false`` ___ <a name="isShort" ></a> ### <code>isShort</code> <code>isShort (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the string value is a short value when given an optional format according to the rules of ``toShort()``. * ``isShort('123') -> true`` * ``isShort('$123' -> '$###') -> true`` * ``isShort('microsoft') -> false`` ___ <a name="isTimestamp" ></a> ### <code>isTimestamp</code> <code>isTimestamp (\<value1\> : string, [<format>: string]) => boolean</code> Checks if the input date string is a time stamp by using an optional input time stamp format. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. If the time stamp is omitted, the default pattern ``yyyy-[M]M-[d]d hh:mm:ss[.f...]`` is used. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The `timestamp` function supports up to millisecond accuracy with a value of `999`. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``isTimestamp('2016-12-31 00:12:00') -> true`` * ``isTimestamp('2016-12-31T00:12:00' -> 'yyyy-MM-dd\\'T\\'HH:mm:ss' -> 'PST') -> true`` * ``isTimestamp('2012-8222.18') -> false`` ___ <a name="isUpdate" ></a> ### <code>isUpdate</code> <code>isUpdate([<value1> : integer]) => boolean</code> Checks if the row is marked for update. For transformations taking more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isUpdate()`` * ``isUpdate(1)`` ___ <a name="isUpsert" ></a> ### <code>isUpsert</code> <code>isUpsert([<value1> : integer]) => boolean</code> Checks if the row is marked for insert. For transformations taking more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either `1` or `2`, and the default value is `1`. * ``isUpsert()`` * ``isUpsert(1)`` ___ ## J <a name="jaroWinkler" ></a> ### <code>jaroWinkler</code> <code>jaroWinkler(<value1> : string, <value2> : string) => double</code> Gets the JaroWinkler distance between two strings. * ``jaroWinkler('frog', 'frog') => 1.0`` ___ ## K <a name="keyValues" ></a> ### <code>keyValues</code> <code>keyValues(<value1> : array, <value2> : array) => map</code> Creates a map of key/values. The first parameter is an array of keys, and the second is the array of values. Both arrays should have equal length. * ``keyValues(['bojjus', 'appa'], ['gunchus', 'ammi']) => ['bojjus' -> 'gunchus', 'appa' -> 'ammi']`` ___ <a name="kurtosis" ></a> ### <code>kurtosis</code> <code>kurtosis(<value1> : number) => double</code> Gets the kurtosis of a column. * ``kurtosis(sales)`` ___ <a name="kurtosisIf" ></a> ### <code>kurtosisIf</code> <code>kurtosisIf(<value1> : boolean, <value2> : number) => double</code> Gets the kurtosis of a column, based on criteria. * ``kurtosisIf(region == 'West', sales)`` ___ ## L <a name="lag" ></a> ### <code>lag</code> <code>lag(<value> : any, [<number of rows to look before> : number], [<default value> : any]) => any</code> Gets the value of the first parameter evaluated `n` rows before the current row. The second parameter is the number of rows to look back, and the default value is `1`. If there aren't as many rows, a value of null is returned unless a default value is specified. * ``lag(amount, 2)`` * ``lag(amount, 2000, 100)`` ___ <a name="last" ></a> ### <code>last</code> <code>last(<value1> : any, [<value2> : boolean]) => any</code> Gets the last value of a column group. If the second parameter `ignoreNulls` is omitted, it's assumed false. * ``last(sales)`` * ``last(sales, false)`` ___ <a name="lastDayOfMonth" ></a> ### <code>lastDayOfMonth</code> <code>lastDayOfMonth(<value1> : datetime) => date</code> Gets the last date of the month when given a date. * ``lastDayOfMonth(toDate('2009-01-12')) -> toDate('2009-01-31')`` ___ <a name="lead" ></a> ### <code>lead</code> <code>lead(<value> : any, [<number of rows to look after> : number], [<default value> : any]) => any</code> Gets the value of the first parameter evaluated `n` rows after the current row. The second parameter is the number of rows to look forward, and the default value is `1`. If there aren't as many rows, a value of null is returned unless a default value is specified. * ``lead(amount, 2)`` * ``lead(amount, 2000, 100)`` ___ <a name="least" ></a> ### <code>least</code> <code>least(<value1> : any, ...) => any</code> Uses the comparison lesser than or equal to operator. Same as the `<=` operator. * ``least(10, 30, 15, 20) -> 10`` * ``least(toDate('2010-12-12'), toDate('2011-12-12'), toDate('2000-12-12')) -> toDate('2000-12-12')`` ___ <a name="left" ></a> ### <code>left</code> <code>left(<string to subset> : string, <number of characters> : integral) => string</code> Extracts a substring start at index `1` with the number of characters. Same as `SUBSTRING(str, 1, n)`. * ``left('bojjus', 2) -> 'bo'`` * ``left('bojjus', 20) -> 'bojjus'`` ___ <a name="length" ></a> ### <code>length</code> <code>length(<value1> : string) => integer</code> Returns the length of the string. * ``length('dumbo') -> 5`` ___ <a name="lesser" ></a> ### <code>lesser</code> <code>lesser(<value1> : any, <value2> : any) => boolean</code> Uses the comparison less operator. Same as the `<` operator. * ``lesser(12, 24) -> true`` * ``('abcd' < 'abc') -> false`` * ``(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') < toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')) -> true`` ___ <a name="lesserOrEqual" ></a> ### <code>lesserOrEqual</code> <code>lesserOrEqual(<value1> : any, <value2> : any) => boolean</code> Uses the comparison lesser than or equal to operator. Same as the `<=` operator. * ``lesserOrEqual(12, 12) -> true`` * ``('dumbo' <= 'dum') -> false`` ___ <a name="levenshtein" ></a> ### <code>levenshtein</code> <code>levenshtein(<from string> : string, <to string> : string) => integer</code> Gets the levenshtein distance between two strings. * ``levenshtein('boys', 'girls') -> 4`` ___ <a name="like" ></a> ### <code>like</code> <code>like(<string> : string, <pattern match> : string) => boolean</code> Uses a string pattern that's matched literally. The exceptions are the following special symbols: _ matches any one character in the input (similar to `.*` in ```posix``` regular expressions). % matches zero or more characters in the input (similar to ```.*``` in ```posix``` regular expressions). The escape character is ''. If an escape character precedes a special symbol or another escape character, the following character is matched literally. It's invalid to escape any other character. * ``like('icecream', 'ice%') -> true`` ___ <a name="locate" ></a> ### <code>locate</code> <code>locate(<substring to find> : string, <string> : string, [<from index - 1-based> : integral]) => integer</code> Finds the position (1 based) of the substring within a string starting at a certain position. If the position is omitted, it starts from the beginning of the string. If not found, `0` is returned. * ``locate('mbo', 'dumbo') -> 3`` * ``locate('o', 'microsoft', 6) -> 7`` * ``locate('bad', 'good') -> 0`` ___ <a name="log" ></a> ### <code>log</code> <code>log(<value1> : number, [<value2> : number]) => double</code> Calculates the log value. You can supply an optional base or else a Euler number if used. * ``log(100, 10) -> 2`` ___ <a name="log10" ></a> ### <code>log10</code> <code>log10(<value1> : number) => double</code> Calculates the log value based on 10 base. * ``log10(100) -> 2`` ___ <a name="lookup" ></a> ### <code>lookup</code> <code>lookup(key, key2, ...) => complex[]</code> Looks up the first row from the cached sink by using the specified keys that match the keys from the cached sink. * ``cacheSink#lookup(movieId)`` ___ <a name="lower" ></a> ### <code>lower</code> <code>lower(<value1> : string) => string</code> Lowercases a string. * ``lower('GunChus') -> 'gunchus'`` ___ <a name="lpad" ></a> ### <code>lpad</code> <code>lpad(<string to pad> : string, <final padded length> : integral, <padding> : string) => string</code> Left pads the string by the supplied padding until the string is of a certain length. If the string is equal to or greater than the length, the string is trimmed to the length. * ``lpad('dumbo', 10, '-') -> '-----dumbo'`` * ``lpad('dumbo', 4, '-') -> 'dumb'`` ___ <a name="ltrim" ></a> ### <code>ltrim</code> <code>ltrim(<string to trim> : string, [<trim characters> : string]) => string</code> Left trims a string of leading characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter. * ``ltrim(' dumbo ') -> 'dumbo '`` * ``ltrim('!--!du!mbo!', '-!') -> 'du!mbo!'`` ___ ## M <a name="map" ></a> ### <code>map</code> <code>map(<value1> : array, <value2> : unaryfunction) => any</code> Maps each element of the array to a new element by using the provided expression. The `map` function expects a reference to one element in the expression function as `#item`. * ``map([1, 2, 3, 4], #item + 2) -> [3, 4, 5, 6]`` * ``map(['a', 'b', 'c', 'd'], #item + '_processed') -> ['a_processed', 'b_processed', 'c_processed', 'd_processed']`` ___ <a name="mapAssociation" ></a> ### <code>mapAssociation</code> <code>mapAssociation(<value1> : map, <value2> : binaryFunction) => array</code> Transforms a map by associating the keys to new values. Returns an array. It takes a mapping function where you can address the item as `#key` and the current value as `#value`. * ``mapAssociation(['bojjus' -> 'gunchus', 'appa' -> 'ammi'], @(key = #key, value = #value)) => [@(key = 'bojjus', value = 'gunchus'), @(key = 'appa', value = 'ammi')]`` ___ <a name="mapIf" ></a> ### <code>mapIf</code> <code>mapIf (\<value1\> : array, \<value2\> : binaryfunction, \<value3\>: binaryFunction) => any</code> Conditionally maps an array to another array of the same or smaller length. The values can be of any data type, including `structTypes`. It takes a mapping function where you can address the item in the array as `#item` and the current index as `#index`. For deeply nested maps, you can refer to the parent maps by using the ``#item_[n]` (`#item_1`, `#index_1`) notation. * ``mapIf([10, 20, 30], #item > 10, #item + 5) -> [25, 35]`` * ``mapIf(['icecream', 'cake', 'soda'], length(#item) > 4, upper(#item)) -> ['ICECREAM', 'CAKE']`` ___ <a name="mapIndex" ></a> ### <code>mapIndex</code> <code>mapIndex(<value1> : array, <value2> : binaryfunction) => any</code> Maps each element of the array to a new element by using the provided expression. The `map` function expects a reference to one element in the expression function as `#item` and a reference to the element index as `#index`. * ``mapIndex([1, 2, 3, 4], #item + 2 + #index) -> [4, 6, 8, 10]`` ___ <a name="mapLoop" ></a> ### <code>mapLoop</code> <code>mapLoop(\<value1\> : integer, \<value2\> : unaryfunction) => any</code> Loops through from `1` to the length to create an array of that length. It takes a mapping function where you can address the index in the array as `#index`. For deeply nested maps, you can refer to the parent maps by using the `#index_n` (`#index_1`, `#index_2`) notation. * ``mapLoop(3, #index * 10) -> [10, 20, 30]`` ___ <a name="max" ></a> ### <code>max</code> <code>max(<value1> : any) => any</code> Gets the maximum value of a column. * ``max(sales)`` ___ <a name="maxIf" ></a> ### <code>maxIf</code> <code>maxIf(<value1> : boolean, <value2> : any) => any</code> Gets the maximum value of a column, based on criteria. * ``maxIf(region == 'West', sales)`` ___ <a name="md5" ></a> ### <code>md5</code> <code>md5(<value1> : any, ...) => string</code> Calculates the MD5 digest of a set of columns of varying primitive data types and returns a 32-character hex string. You can use it to calculate a fingerprint for a row. * ``md5(5, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> '4ce8a880bd621a1ffad0bca905e1bc5a'`` ___ <a name="mean" ></a> ### <code>mean</code> <code>mean(<value1> : number) => number</code> Gets the mean of values of a column. Same as `AVG`. * ``mean(sales)`` ___ <a name="meanIf" ></a> ### <code>meanIf</code> <code>meanIf(<value1> : boolean, <value2> : number) => number</code> Gets the mean of values of a column, based on criteria. Same as `avgIf`. * ``meanIf(region == 'West', sales)`` ___ <a name="millisecond" ></a> ### <code>millisecond</code> <code>millisecond(<value1> : timestamp, [<value2> : string]) => integer</code> Gets the millisecond value of a date. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``millisecond(toTimestamp('2009-07-30 12:58:59.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> 871`` ___ <a name="milliseconds" ></a> ### <code>milliseconds</code> <code>milliseconds(<value1> : integer) => long</code> Gets the duration in milliseconds for the number of milliseconds. * ``milliseconds(2) -> 2L`` ___ <a name="min" ></a> ### <code>min</code> <code>min(<value1> : any) => any</code> Gets the minimum value of a column. * ``min(sales)`` ___ <a name="minIf" ></a> ### <code>minIf</code> <code>minIf(<value1> : boolean, <value2> : any) => any</code> Gets the minimum value of a column, based on criteria. * ``minIf(region == 'West', sales)`` ___ <a name="minus" ></a> ### <code>minus</code> <code>minus(<value1> : any, <value2> : any) => any</code> Subtracts numbers. Subtracts the number of days from a date. Subtracts duration from a time stamp. Subtracts two time stamps to get the difference in milliseconds. Same as the `-` operator. * ``minus(20, 10) -> 10`` * ``20 - 10 -> 10`` * ``minus(toDate('2012-12-15'), 3) -> toDate('2012-12-12')`` * ``toDate('2012-12-15') - 3 -> toDate('2012-12-12')`` * ``toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') + (days(1) + hours(2) - seconds(10)) -> toTimestamp('2019-02-04 07:19:18.871', 'yyyy-MM-dd HH:mm:ss.SSS')`` * ``toTimestamp('2019-02-03 05:21:34.851', 'yyyy-MM-dd HH:mm:ss.SSS') - toTimestamp('2019-02-03 05:21:36.923', 'yyyy-MM-dd HH:mm:ss.SSS') -> -2072`` ___ <a name="minute" ></a> ### <code>minute</code> <code>minute(<value1> : timestamp, [<value2> : string]) => integer</code> Gets the minute value of a time stamp. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``minute(toTimestamp('2009-07-30 12:58:59')) -> 58`` * ``minute(toTimestamp('2009-07-30 12:58:59'), 'PST') -> 58`` ___ <a name="minutes" ></a> ### <code>minutes</code> <code>minutes(<value1> : integer) => long</code> Gets the duration in milliseconds for the number of minutes. * ``minutes(2) -> 120000L`` ___ <a name="mlookup" ></a> ### <code>mlookup</code> <code>mlookup(key, key2, ...) => complex[]</code> Looks up all the matching rows from the cached sink by using the specified keys that match the keys from the cached sink. * ``cacheSink#mlookup(movieId)`` ___ <a name="mod" ></a> ### <code>mod</code> <code>mod(<value1> : any, <value2> : any) => any</code> Gets the modulus of a pair of numbers. Same as the `%` operator. * ``mod(20, 8) -> 4`` * ``20 % 8 -> 4`` ___ <a name="month" ></a> ### <code>month</code> <code>month(<value1> : datetime) => integer</code> Gets the month value of a date or time stamp. * ``month(toDate('2012-8-8')) -> 8`` ___ <a name="monthsBetween" ></a> ### <code>monthsBetween</code> <code>monthsBetween(<from date/timestamp> : datetime, <to date/timestamp> : datetime, [<roundoff> : boolean], [<time zone> : string]) => double</code> Gets the number of months between two dates. You can round off the calculation. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``monthsBetween(toTimestamp('1997-02-28 10:30:00'), toDate('1996-10-30')) -> 3.94959677`` ___ <a name="multiply" ></a> ### <code>multiply</code> <code>multiply(<value1> : any, <value2> : any) => any</code> Multiplies a pair of numbers. Same as the `*` operator. * ``multiply(20, 10) -> 200`` * ``20 * 10 -> 200`` ___ ## N <a name="negate" ></a> ### <code>negate</code> <code>negate(<value1> : number) => number</code> Negates a number. Turns positive numbers to negative and vice versa. * ``negate(13) -> -13`` ___ <a name="nextSequence" ></a> ### <code>nextSequence</code> <code>nextSequence() => long</code> Returns the next unique sequence. The number is consecutive only within a partition and is prefixed by the partition ID. * ``nextSequence() == 12313112 -> false`` ___ <a name="normalize" ></a> ### <code>normalize</code> <code>normalize(<String to normalize> : string) => string</code> Normalizes the string value to separate accented Unicode characters. * ``regexReplace(normalize('bo²s'), `\p{M}`, '') -> 'boys'`` ___ <a name="not" ></a> ### <code>not</code> <code>not(<value1> : boolean) => boolean</code> Uses the logical negation operator. * ``not(true) -> false`` * ``not(10 == 20) -> true`` ___ <a name="notEquals" ></a> ### <code>notEquals</code> <code>notEquals(<value1> : any, <value2> : any) => boolean</code> Uses the comparison not equals operator. Same as the `!=` operator. * ``12 != 24 -> true`` * ``'bojjus' != 'bo' + 'jjus' -> false`` ___ <a name="nTile" ></a> ### <code>nTile</code> <code>nTile([<value1> : integer]) => integer</code> The ```nTile``` function divides the rows for each window partition into `n` buckets ranging from `1` to at most `n`. Bucket values differ by, at most, `1`. If the number of rows in the partition doesn't divide evenly into the number of buckets, then the remainder values are distributed one per bucket, starting with the first bucket. The ```nTile``` function is useful for the calculation of ```tertiles```, quartiles, deciles, and other common summary statistics. The function calculates two variables during initialization. The size of a regular bucket has one extra row added to it. Both variables are based on the size of the current partition. During the calculation process, the function keeps track of the current row number, the current bucket number, and the row number at which the bucket changes (`bucketThreshold`). When the current row number reaches the bucket threshold, the bucket value increases by one. The threshold increases by the bucket size (plus one extra if the current bucket is padded). * ``nTile()`` * ``nTile(numOfBuckets)`` ___ <a name="null" ></a> ### <code>null</code> <code>null() => null</code> Returns a `NULL` value. Use the function `syntax(null())` if a column is named `null`. Any operation that uses null results in a `NULL` value. * ``isNull('dumbo' + `null`) -> true`` * ``isNull(10 * `null`) -> true`` * ``isNull('') -> false`` * ``isNull(10 + 20) -> false`` * ``isNull(10/0) -> true`` ___ ## O <a name="or" ></a> ### <code>or</code> <code>or(<value1> : boolean, <value2> : boolean) => boolean</code> Uses the logical `OR` operator. Same as `||`. * ``or(true, false) -> true`` * ``true || false -> true`` ___ <a name="originColumns" ></a> ### <code>originColumns</code> <code>originColumns(<streamName> : string) => any</code> Gets all output columns for an origin stream where columns were created. Must be enclosed in another function. * ``array(toString(originColumns('source1')))`` ___ <a name="output" ></a> ### <code>output</code> <code>output() => any</code> Returns the first row of the results of the cache sink. * ``cacheSink#output()`` ___ <a name="outputs" ></a> ### <code>outputs</code> <code>output() => any</code> Returns the entire output row set of the results of the cache sink. * ``cacheSink#outputs()`` ___ ## P <a name="partitionId" ></a> ### <code>partitionId</code> <code>partitionId() => integer</code> Returns the current partition ID that the input row is in. * ``partitionId()`` ___ <a name="pMod" ></a> ### <code>pMod</code> <code>pMod(<value1> : any, <value2> : any) => any</code> Gives the positive modulus of a pair of numbers. * ``pmod(-20, 8) -> 4`` ___ <a name="power" ></a> ### <code>power</code> <code>power(<value1> : number, <value2> : number) => double</code> Raises one number to the power of another. * ``power(10, 2) -> 100`` ___ ## R <a name="radians" ></a> ### <code>radians</code> <code>radians(<value1> : number) => double</code> Converts degrees to radians. * ``radians(180) => 3.141592653589793`` ___ <a name="random" ></a> ### <code>random</code> <code>random(<value1> : integral) => double</code> Returns a random number when given a seed within a partition. The seed should be a fixed value and is used with the partition ID to produce random values in a range (`0.0-1.0`). * ``random(1) == 1 -> false`` ___ <a name="rank" ></a> ### <code>rank</code> <code>rank() => integer</code> Computes the rank of a value in a group of values specified in a window's order by clause. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values produce gaps in the sequence. The `rank` function works even when data isn't sorted and looks for change in values. * ``rank()`` ___ <a name="reassociate" ></a> ### <code>reassociate</code> <code>reassociate(<value1> : map, <value2> : binaryFunction) => map</code> Transforms a map by associating the keys to new values. It takes a mapping function where you can address the item as `#key` and the current value as `#value`. * ``reassociate(['fruit' -> 'apple', 'vegetable' -> 'tomato'], substring(#key, 1, 1) + substring(#value, 1, 1)) => ['fruit' -> 'fa', 'vegetable' -> 'vt']`` ___ <a name="reduce" ></a> ### <code>reduce</code> <code>reduce(<value1> : array, <value2> : any, <value3> : binaryfunction, <value4> : unaryfunction) => any</code> Accumulates elements in an array. The `reduce` function expects a reference to an accumulator and one element in the first expression function as `#acc `and `#item`. It expects the resulting value as `#result` to be used in the second expression function. * ``toString(reduce(['1', '2', '3', '4'], '0', #acc + #item, #result)) -> '01234'`` ___ <a name="regexExtract" ></a> ### <code>regexExtract</code> <code>regexExtract(<string> : string, <regex to find> : string, [<match group 1-based index> : integral]) => string</code> Extracts a matching substring for a given regex pattern. The last parameter identifies the match group and defaults to `1` if omitted. Use `<regex>` to match a string without escaping. Index `0` returns all matches. Without match groups, index `1` and above don't return any result. * ``regexExtract('Cost is between 600 and 800 dollars', '(\\d+) and (\\d+)', 2) -> '800'`` * ``regexExtract('Cost is between 600 and 800 dollars', `(\d+) and (\d+)`, 2) -> '800'`` ___ <a name="regexMatch" ></a> ### <code>regexMatch</code> <code>regexMatch(<string> : string, <regex to match> : string) => boolean</code> Checks if the string matches the given regex pattern. Use `<regex>` to match a string without escaping. * ``regexMatch('200.50', '(\\d+).(\\d+)') -> true`` * ``regexMatch('200.50', `(\d+).(\d+)`) -> true`` ___ <a name="regexReplace" ></a> ### <code>regexReplace</code> <code>regexReplace(<string> : string, <regex to find> : string, <substring to replace> : string) => string</code> Replaces all occurrences of a regex pattern with another substring in the specific string. Use `<regex>` to match a string without escaping. * ``regexReplace('100 and 200', '(\\d+)', 'bojjus') -> 'bojjus and bojjus'`` * ``regexReplace('100 and 200', `(\d+)`, 'gunchus') -> 'gunchus and gunchus'`` ___ <a name="regexSplit" ></a> ### <code>regexSplit</code> <code>regexSplit(<string to split> : string, <regex expression> : string) => array</code> Splits a string based on a delimiter based on regex and returns an array of strings. * ``regexSplit('bojjusAgunchusBdumbo', `[CAB]`) -> ['bojjus', 'gunchus', 'dumbo']`` * ``regexSplit('bojjusAgunchusBdumboC', `[CAB]`) -> ['bojjus', 'gunchus', 'dumbo', '']`` * ``(regexSplit('bojjusAgunchusBdumboC', `[CAB]`)[1]) -> 'bojjus'`` * ``isNull(regexSplit('bojjusAgunchusBdumboC', `[CAB]`)[20]) -> true`` ___ <a name="replace" ></a> ### <code>replace</code> <code>replace(<string> : string, <substring to find> : string, [<substring to replace> : string]) => string</code> Replaces all occurrences of a substring with another substring in the specific string. If the last parameter is omitted, it defaults to an empty string. * ``replace('doggie dog', 'dog', 'cat') -> 'catgie cat'`` * ``replace('doggie dog', 'dog', '') -> 'gie '`` * ``replace('doggie dog', 'dog') -> 'gie '`` ___ <a name="reverse" ></a> ### <code>reverse</code> <code>reverse(<value1> : string) => string</code> Reverses a string. * ``reverse('gunchus') -> 'suhcnug'`` ___ <a name="right" ></a> ### <code>right</code> <code>right(<string to subset> : string, <number of characters> : integral) => string</code> Extracts a substring with a number of characters from the right. Same as `SUBSTRING(str, LENGTH(str) - n, n)`. * ``right('bojjus', 2) -> 'us'`` * ``right('bojjus', 20) -> 'bojjus'`` ___ <a name="rlike" ></a> ### <code>rlike</code> <code>rlike(<string> : string, <pattern match> : string) => boolean</code> Checks if the string matches the given regex pattern. * ``rlike('200.50', `(\d+).(\d+)`) -> true`` * ``rlike('bogus', `M[0-9]+.*`) -> false`` ___ <a name="round" ></a> ### <code>round</code> <code>round(<number> : number, [<scale to round> : number], [<rounding option> : integral]) => double</code> Rounds a number when given an optional scale and an optional rounding mode. If the scale is omitted, it defaults to `0`. If the mode is omitted, it defaults to `ROUND_HALF_UP(5)`. The values for rounding include: 1. `ROUND_UP`: Rounding mode to round away from zero. 1. `ROUND_DOWN`: Rounding mode to round toward zero. 1. `ROUND_CEILING`: Rounding mode to round toward positive infinity. (Same as `ROUND_UP` if input is positive. If negative, it behaves as `ROUND_DOWN`. For example, -1.1 would be -1.0 with `ROUND_CEILING` and -2 with `ROUND_UP`.) 1. `ROUND_FLOOR`: Rounding mode to round toward negative infinity. (Same as `ROUND_DOWN` if input is positive. If negative, it behaves as `ROUND_UP`.) 1. `ROUND_HALF_UP`: Rounding mode to round toward "nearest neighbor" unless both neighbors are equidistant, in which case it behaves as `ROUND_UP`. (Most common + default for Dataflow.) 1. `ROUND_HALF_DOWN`: Rounding mode to round toward "nearest neighbor" unless both neighbors are equidistant, in which case `ROUND_DOWN`. 1. `ROUND_HALF_EVEN`: Rounding mode to round toward the "nearest neighbor" unless both neighbors are equidistant, in which case, round toward the even neighbor. 1. `ROUND_UNNECESSARY`: Rounding mode to assert that the round operation has an exact result, so no rounding is necessary. * ``round(100.123) -> 100.0`` * ``round(2.5, 0) -> 3.0`` * ``round(5.3999999999999995, 2, 7) -> 5.40`` ___ <a name="rowNumber" ></a> ### <code>rowNumber</code> <code>rowNumber() => integer</code> Assigns a sequential row numbering for rows in a window starting with 1. * ``rowNumber()`` <a name="rpad" ></a> ### <code>rpad</code> <code>rpad(<string to pad> : string, <final padded length> : integral, <padding> : string) => string</code> Right pads the string by the supplied padding until the string is of a certain length. If the string is equal to or greater than the length, the string is trimmed to the length. * ``rpad('dumbo', 10, '-') -> 'dumbo-----'`` * ``rpad('dumbo', 4, '-') -> 'dumb'`` * ``rpad('dumbo', 8, '<>') -> 'dumbo<><'`` ___ <a name="rtrim" ></a> ### <code>rtrim</code> <code>rtrim(<string to trim> : string, [<trim characters> : string]) => string</code> Right trims a string of trailing characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter. * ``rtrim(' dumbo ') -> ' dumbo'`` * ``rtrim('!--!du!mbo!', '-!') -> '!--!du!mbo'`` ___ ## S <a name="second" ></a> ### <code>second</code> <code>second(<value1> : timestamp, [<value2> : string]) => integer</code> Gets the second value of a date. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The local time zone is used as the default. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``second(toTimestamp('2009-07-30 12:58:59')) -> 59`` ___ <a name="seconds" ></a> ### <code>seconds</code> <code>seconds(<value1> : integer) => long</code> Gives the duration in milliseconds for the number of seconds. * ``seconds(2) -> 2000L`` ___ <a name="setBitSet" ></a> ### <code>setBitSet</code> <code>setBitSet (\<value1\>: array, \<value2\>:array) => array</code> Sets bit positions in this bitset. * ``setBitSet(toBitSet([10, 32]), [98]) => [4294968320L, 17179869184L]`` ___ <a name="sha1" ></a> ### <code>sha1</code> <code>sha1(<value1> : any, ...) => string</code> Calculates the SHA-1 digest of a set of columns of varying primitive data types and returns a 40-character hex string. You can use it to calculate a fingerprint for a row. * ``sha1(5, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> '46d3b478e8ec4e1f3b453ac3d8e59d5854e282bb'`` ___ <a name="sha2" ></a> ### <code>sha2</code> <code>sha2(<value1> : integer, <value2> : any, ...) => string</code> Calculates the SHA-2 digest of a set of columns of varying primitive data types when given a bit length, which can only be of values `0(256)`, `224`, `256`, `384`, and `512`. You can use it to calculate a fingerprint for a row. * ``sha2(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> 'afe8a553b1761c67d76f8c31ceef7f71b66a1ee6f4e6d3b5478bf68b47d06bd3'`` ___ <a name="sin" ></a> ### <code>sin</code> <code>sin(<value1> : number) => double</code> Calculates a sine value. * ``sin(2) -> 0.9092974268256817`` ___ <a name="sinh" ></a> ### <code>sinh</code> <code>sinh(<value1> : number) => double</code> Calculates a hyperbolic sine value. * ``sinh(0) -> 0.0`` ___ <a name="size" ></a> ### <code>size</code> <code>size(<value1> : any) => integer</code> Finds the size of an array or map type. * ``size(['element1', 'element2']) -> 2`` * ``size([1,2,3]) -> 3`` ___ <a name="skewness" ></a> ### <code>skewness</code> <code>skewness(<value1> : number) => double</code> Gets the skewness of a column. * ``skewness(sales)`` ___ <a name="skewnessIf" ></a> ### <code>skewnessIf</code> <code>skewnessIf(<value1> : boolean, <value2> : number) => double</code> Gets the skewness of a column, based on criteria. * ``skewnessIf(region == 'West', sales)`` ___ <a name="slice" ></a> ### <code>slice</code> <code>slice(<array to slice> : array, <from 1-based index> : integral, [<number of items> : integral]) => array</code> Extracts a subset of an array from a position. The position is 1 based. If the length is omitted, it defaults to the end of the string. * ``slice([10, 20, 30, 40], 1, 2) -> [10, 20]`` * ``slice([10, 20, 30, 40], 2) -> [20, 30, 40]`` * ``slice([10, 20, 30, 40], 2)[1] -> 20`` * ``isNull(slice([10, 20, 30, 40], 2)[0]) -> true`` * ``isNull(slice([10, 20, 30, 40], 2)[20]) -> true`` * ``slice(['a', 'b', 'c', 'd'], 8) -> []`` ___ <a name="sort" ></a> ### <code>sort</code> <code>sort(<value1> : array, <value2> : binaryfunction) => array</code> Sorts the array by using the provided predicate function. The `sort` function expects a reference to two consecutive elements in the expression function as `#item1` and `#item2`. * ``sort([4, 8, 2, 3], compare(#item1, #item2)) -> [2, 3, 4, 8]`` * ``sort(['a3', 'b2', 'c1'], iif(right(#item1, 1) >= right(#item2, 1), 1, -1)) -> ['c1', 'b2', 'a3']`` ___ <a name="soundex" ></a> ### <code>soundex</code> <code>soundex(<value1> : string) => string</code> Gets the ```soundex``` code for the string. * ``soundex('genius') -> 'G520'`` ___ <a name="split" ></a> ### <code>split</code> <code>split(<string to split> : string, <split characters> : string) => array</code> Splits a string based on a delimiter and returns an array of strings. * ``split('bojjus,guchus,dumbo', ',') -> ['bojjus', 'guchus', 'dumbo']`` * ``split('bojjus,guchus,dumbo', '|') -> ['bojjus,guchus,dumbo']`` * ``split('bojjus, guchus, dumbo', ', ') -> ['bojjus', 'guchus', 'dumbo']`` * ``split('bojjus, guchus, dumbo', ', ')[1] -> 'bojjus'`` * ``isNull(split('bojjus, guchus, dumbo', ', ')[0]) -> true`` * ``isNull(split('bojjus, guchus, dumbo', ', ')[20]) -> true`` * ``split('bojjusguchusdumbo', ',') -> ['bojjusguchusdumbo']`` ___ <a name="sqrt" ></a> ### <code>sqrt</code> <code>sqrt(<value1> : number) => double</code> Calculates the square root of a number. * ``sqrt(9) -> 3`` ___ <a name="startsWith" ></a> ### <code>startsWith</code> <code>startsWith(<string> : string, <substring to check> : string) => boolean</code> Checks if the string starts with the supplied string. * ``startsWith('dumbo', 'du') -> true`` ___ <a name="stddev" ></a> ### <code>stddev</code> <code>stddev(<value1> : number) => double</code> Gets the standard deviation of a column. * ``stdDev(sales)`` ___ <a name="stddevIf" ></a> ### <code>stddevIf</code> <code>stddevIf(<value1> : boolean, <value2> : number) => double</code> Gets the standard deviation of a column, based on criteria. * ``stddevIf(region == 'West', sales)`` ___ <a name="stddevPopulation" ></a> ### <code>stddevPopulation</code> <code>stddevPopulation(<value1> : number) => double</code> Gets the population standard deviation of a column. * ``stddevPopulation(sales)`` ___ <a name="stddevPopulationIf" ></a> ### <code>stddevPopulationIf</code> <code>stddevPopulationIf(<value1> : boolean, <value2> : number) => double</code> Gets the population standard deviation of a column, based on criteria. * ``stddevPopulationIf(region == 'West', sales)`` ___ <a name="stddevSample" ></a> ### <code>stddevSample</code> <code>stddevSample(<value1> : number) => double</code> Gets the sample standard deviation of a column. * ``stddevSample(sales)`` ___ <a name="stddevSampleIf" ></a> ### <code>stddevSampleIf</code> <code>stddevSampleIf(<value1> : boolean, <value2> : number) => double</code> Gets the sample standard deviation of a column, based on criteria. * ``stddevSampleIf(region == 'West', sales)`` ___ <a name="subDays" ></a> ### <code>subDays</code> <code>subDays(<date/timestamp> : datetime, <days to subtract> : integral) => datetime</code> Subtracts days from a date or time stamp. Same as the `-` operator for date. * ``subDays(toDate('2016-08-08'), 1) -> toDate('2016-08-07')`` ___ <a name="subMonths" ></a> ### <code>subMonths</code> <code>subMonths(<date/timestamp> : datetime, <months to subtract> : integral) => datetime</code> Subtracts months from a date or time stamp. * ``subMonths(toDate('2016-09-30'), 1) -> toDate('2016-08-31')`` ___ <a name="substring" ></a> ### <code>substring</code> <code>substring(<string to subset> : string, <from 1-based index> : integral, [<number of characters> : integral]) => string</code> Extracts a substring of a certain length from a position. The position is 1 based. If the length is omitted, it defaults to the end of the string. * ``substring('Cat in the hat', 5, 2) -> 'in'`` * ``substring('Cat in the hat', 5, 100) -> 'in the hat'`` * ``substring('Cat in the hat', 5) -> 'in the hat'`` * ``substring('Cat in the hat', 100, 100) -> ''`` ___ <a name="substringIndex" ></a> ### <code>substringIndex</code> <code>substringIndex(<string to subset> : string, <delimiter> : string, <count of delimiter occurrences> : integral]) => string</code> Extracts the substring before count occurrences of the delimiter. If the count is positive, everything to the left of the final delimiter (counting from the left) is returned. If the count is negative, everything to the right of the final delimiter (counting from the right) is returned. * ``substringIndex('111-222-333', '-', 1) -> '111'`` * ``substringIndex('111-222-333', '-', 2) -> '111-222'`` * ``substringIndex('111-222-333', '-', -1) -> '333'`` * ``substringIndex('111-222-333', '-', -2) -> '222-333'`` ___ <a name="sum" ></a> ### <code>sum</code> <code>sum(<value1> : number) => number</code> Gets the aggregate sum of a numeric column. * ``sum(col)`` ___ <a name="sumDistinct" ></a> ### <code>sumDistinct</code> <code>sumDistinct(<value1> : number) => number</code> Gets the aggregate sum of distinct values of a numeric column. * ``sumDistinct(col)`` ___ <a name="sumDistinctIf" ></a> ### <code>sumDistinctIf</code> <code>sumDistinctIf(<value1> : boolean, <value2> : number) => number</code> Gets the aggregate sum of a numeric column, based on criteria. The condition can be based on any column. * ``sumDistinctIf(state == 'CA' && commission < 10000, sales)`` * ``sumDistinctIf(true, sales)`` ___ <a name="sumIf" ></a> ### <code>sumIf</code> <code>sumIf(<value1> : boolean, <value2> : number) => number</code> Gets the aggregate sum of a numeric column, based on criteria. The condition can be based on any column. * ``sumIf(state == 'CA' && commission < 10000, sales)`` * ``sumIf(true, sales)`` ___ ## T <a name="tan" ></a> ### <code>tan</code> <code>tan(<value1> : number) => double</code> Calculates a tangent value. * ``tan(0) -> 0.0`` ___ <a name="tanh" ></a> ### <code>tanh</code> <code>tanh(<value1> : number) => double</code> Calculates a hyperbolic tangent value. * ``tanh(0) -> 0.0`` ___ <a name="toBase64" ></a> ### <code>toBase64</code> <code>toBase64(<value1> : string, <encoding type> : string]) => string</code> Encodes the specific string in base64. You can optionally pass the encoding type. * ``toBase64('bojjus') -> 'Ym9qanVz'`` * ``toBase64('± 25000, € 5.000,- |', 'Windows-1252') -> 'sSAyNTAwMCwggCA1LjAwMCwtIHw='`` ___ <a name="toBinary" ></a> ### <code>toBinary</code> <code>toBinary(<value1> : any) => binary</code> Converts any numeric, date, time stamp, or string to binary representation. * ``toBinary(3) -> [0x11]`` ___ <a name="toBoolean" ></a> ### <code>toBoolean</code> <code>toBoolean(<value1> : string) => boolean</code> Converts a value of (`t`, `true`, `y`, `yes`, `1`) to true and (`f`, `false`, `n`, `no`, `0`) to `false` and `NULL` for any other value. * ``toBoolean('true') -> true`` * ``toBoolean('n') -> false`` * ``isNull(toBoolean('truthy')) -> true`` ___ <a name="toByte" ></a> ### <code>toByte</code> <code>toByte(<value> : any, [<format> : string], [<locale> : string]) => byte</code> Converts any numeric or string to a byte value. You can use an optional Java decimal format for the conversion. * ``toByte(123)`` * ``123`` * ``toByte(0xFF)`` * ``-1`` * ``toByte('123')`` * ``123`` ___ <a name="toDate" ></a> ### <code>toDate</code> <code>toDate(<string> : any, [<date format> : string]) => date</code> Converts an input date string to date by using an optional input date format. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. If the input date format is omitted, the default format is `yyyy-[M]M-[d]d`. Accepted formats are `:[ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ]`. * ``toDate('2012-8-18') -> toDate('2012-08-18')`` * ``toDate('12/18/2012', 'MM/dd/yyyy') -> toDate('2012-12-18')`` ___ <a name="toDecimal" ></a> ### <code>toDecimal</code> <code>toDecimal(<value> : any, [<precision> : integral], [<scale> : integral], [<format> : string], [<locale> : string]) => decimal(10,0)</code> Converts any numeric or string to a decimal value. If precision and scale aren't specified, it defaults to (`10,2`). You can use an optional Java decimal format for the conversion. Use an optional locale format in the form of a BCP47 language like en-US, de, or zh-CN. * ``toDecimal(123.45) -> 123.45`` * ``toDecimal('123.45', 8, 4) -> 123.4500`` * ``toDecimal('$123.45', 8, 4,'$###.00') -> 123.4500`` * ``toDecimal('Ç123,45', 10, 2, 'Ç###,##', 'de') -> 123.45`` ___ <a name="toDouble" ></a> ### <code>toDouble</code> <code>toDouble(<value> : any, [<format> : string], [<locale> : string]) => double</code> Converts any numeric or string to a double value. You can use an optional Java decimal format for the conversion. Use an optional locale format in the form of a BCP47 language like en-US, de, or zh-CN. * ``toDouble(123.45) -> 123.45`` * ``toDouble('123.45') -> 123.45`` * ``toDouble('$123.45', '$###.00') -> 123.45`` * ``toDouble('Ç123,45', 'Ç###,##', 'de') -> 123.45`` ___ <a name="toFloat" ></a> ### <code>toFloat</code> <code>toFloat(<value> : any, [<format> : string], [<locale> : string]) => float</code> Converts any numeric or string to a float value. You can use an optional Java decimal format for the conversion. Truncates any double. * ``toFloat(123.45) -> 123.45f`` * ``toFloat('123.45') -> 123.45f`` * ``toFloat('$123.45', '$###.00') -> 123.45f`` ___ <a name="toInteger" ></a> ### <code>toInteger</code> <code>toInteger(<value> : any, [<format> : string], [<locale> : string]) => integer</code> Converts any numeric or string to an integer value. You can use an optional Java decimal format for the conversion. Truncates any long, float, double. * ``toInteger(123) -> 123`` * ``toInteger('123') -> 123`` * ``toInteger('$123', '$###') -> 123`` ___ <a name="toLong" ></a> ### <code>toLong</code> <code>toLong(<value> : any, [<format> : string], [<locale> : string]) => long</code> Converts any numeric or string to a long value. You can use an optional Java decimal format for the conversion. Truncates any float, double. * ``toLong(123) -> 123`` * ``toLong('123') -> 123`` * ``toLong('$123', '$###') -> 123`` ___ <a name="topN" ></a> ### <code>topN</code> <code>topN(<column/expression> : any, <count> : long, <n> : integer) => array</code> Gets the top `N` values for this column based on the count argument. * ``topN(custId, count, 5)`` * ``topN(productId, num_sales, 10)`` ___ <a name="toShort" ></a> ### <code>toShort</code> <code>toShort(<value> : any, [<format> : string], [<locale> : string]) => short</code> Converts any numeric or string to a short value. You can use an optional Java decimal format for the conversion. Truncates any integer, long, float, double. * ``toShort(123) -> 123`` * ``toShort('123') -> 123`` * ``toShort('$123', '$###') -> 123`` ___ <a name="toString" ></a> ### <code>toString</code> <code>toString(<value> : any, [<number format/date format> : string], [<date locale> : string]) => string</code> Converts a primitive data type to a string. You can specify a format for numbers and date. If unspecified, the system default is picked. Java decimal format is used for numbers. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. The default format is `yyyy-MM-dd`. For a date or time stamp, you can optionally specify a locale. * ``toString(10) -> '10'`` * ``toString('engineer') -> 'engineer'`` * ``toString(123456.789, '##,###.##') -> '123,456.79'`` * ``toString(123.78, '000000.000') -> '000123.780'`` * ``toString(12345, '##0.#####E0') -> '12.345E3'`` * ``toString(toDate('2018-12-31')) -> '2018-12-31'`` * ``isNull(toString(toDate('2018-12-31', 'MM/dd/yy'))) -> true`` * ``toString(4 == 20) -> 'false'`` * ``toString(toDate('12/31/18', 'MM/dd/yy', 'es-ES'), 'MM/dd/yy', 'de-DE')`` ___ <a name="toTimestamp" ></a> ### <code>toTimestamp</code> <code>toTimestamp(<string> : any, [<timestamp format> : string], [<time zone> : string]) => timestamp</code> Converts a string to a time stamp when given an optional time-stamp format. If the time stamp is omitted, the default pattern `yyyy-[M]M-[d]d hh:mm:ss[.f...]` is used. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. The `timestamp` function supports up to millisecond accuracy with a value of `999`. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``toTimestamp('2016-12-31 00:12:00') -> toTimestamp('2016-12-31 00:12:00')`` * ``toTimestamp('2016-12-31T00:12:00', 'yyyy-MM-dd\'T\'HH:mm:ss', 'PST') -> toTimestamp('2016-12-31 00:12:00')`` * ``toTimestamp('12/31/2016T00:12:00', 'MM/dd/yyyy\'T\'HH:mm:ss') -> toTimestamp('2016-12-31 00:12:00')`` * ``millisecond(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> 871`` ___ <a name="toUTC" ></a> ### <code>toUTC</code> <code>toUTC(<value1> : timestamp, [<value2> : string]) => timestamp</code> Converts the time stamp to UTC. You can pass an optional time zone in the form of `GMT`, `PST`, `UTC`, or `America/Cayman`. It defaults to the current time zone. Refer to Java's [SimpleDateFormat](https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) class for available formats. * ``toUTC(currentTimestamp()) == toTimestamp('2050-12-12 19:18:12') -> false`` * ``toUTC(currentTimestamp(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true`` <a name="translate" ></a> ### <code>translate</code> <code>translate(<string to translate> : string, <lookup characters> : string, <replace characters> : string) => string</code> Replace one set of characters by another set of characters in the string. Characters have a one to one replacement. * ``translate('(bojjus)', '()', '[]') -> '[bojjus]'`` * ``translate('(gunchus)', '()', '[') -> '[gunchus'`` ___ <a name="trim" ></a> ### <code>trim</code> <code>trim(<string to trim> : string, [<trim characters> : string]) => string</code> Trims a string of leading and trailing characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter. * ``trim(' dumbo ') -> 'dumbo'`` * ``trim('!--!du!mbo!', '-!') -> 'dumbo'`` ___ <a name="true" ></a> ### <code>true</code> <code>true() => boolean</code> Always returns a true value. Use the function `syntax(true())` if a column is named `true`. * ``(10 + 20 == 30) -> true`` * ``(10 + 20 == 30) -> true()`` ___ <a name="typeMatch" ></a> ### <code>typeMatch</code> <code>typeMatch(<type> : string, <base type> : string) => boolean</code> Matches the type of the column. You can use it in pattern expressions only. The `number` function matches short, integer, long, double, float, or decimal. The `integral` function matches short, integer, long. The `fractional` function matches double, float, decimal. The `datetime` function matches the date or time stamp type. * ``typeMatch(type, 'number')`` * ``typeMatch('date', 'datetime')`` ___ ## U <a name="unescape" ></a> ### <code>unescape</code> <code>unescape(<string_to_escape> : string, <format> : string) => string</code> Unescapes a string according to a format. Literal values for acceptable formats are `json`, `xml`, `ecmascript`, `html`, and `java`. * ```unescape('{\\\\\"value\\\\\": 10}', 'json')``` * ```'{\\\"value\\\": 10}'``` ___ <a name="unfold" ></a> ### <code>unfold</code> <code>unfold (<value1>: array) => any</code> Unfolds an array into a set of rows and repeats the values for the remaining columns in every row. * ``unfold(addresses) => any`` * ``unfold( @(name = salesPerson, sales = salesAmount) ) => any`` ___ <a name="unhex" ></a> ### <code>unhex</code> <code>unhex(\<value1\>: string) => binary</code> Unhexes a binary value from its string representation. You can use it with `sha2`, `md5` to convert from string to binary representation. * ``unhex('1fadbe') -> toBinary([toByte(0x1f), toByte(0xad), toByte(0xbe)])`` * ``unhex(md5(5, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4'))) -> toBinary([toByte(0x4c),toByte(0xe8),toByte(0xa8),toByte(0x80),toByte(0xbd),toByte(0x62),toByte(0x1a),toByte(0x1f),toByte(0xfa),toByte(0xd0),toByte(0xbc),toByte(0xa9),toByte(0x05),toByte(0xe1),toByte(0xbc),toByte(0x5a)])`` <a name="union" ></a> ### <code>union</code> <code>union(<value1>: array, <value2> : array) => array</code> Returns a union set of distinct items from two arrays. * ``union([10, 20, 30], [20, 40]) => [10, 20, 30, 40]`` ___ <a name="upper" ></a> ### <code>upper</code> <code>upper(<value1> : string) => string</code> Uppercases a string. * ``upper('bojjus') -> 'BOJJUS'`` ___ <a name="uuid" ></a> ### <code>uuid</code> <code>uuid() => string</code> Returns the generated UUID. * ``uuid()`` ___ ## V <a name="variance" ></a> ### <code>variance</code> <code>variance(<value1> : number) => double</code> Gets the variance of a column. * ``variance(sales)`` ___ <a name="varianceIf" ></a> ### <code>varianceIf</code> <code>varianceIf(<value1> : boolean, <value2> : number) => double</code> Gets the variance of a column, based on criteria. * ``varianceIf(region == 'West', sales)`` ___ <a name="variancePopulation" ></a> ### <code>variancePopulation</code> <code>variancePopulation(<value1> : number) => double</code> Gets the population variance of a column. * ``variancePopulation(sales)`` ___ <a name="variancePopulationIf" ></a> ### <code>variancePopulationIf</code> <code>variancePopulationIf(<value1> : boolean, <value2> : number) => double</code> Gets the population variance of a column, based on criteria. * ``variancePopulationIf(region == 'West', sales)`` ___ <a name="varianceSample" ></a> ### <code>varianceSample</code> <code>varianceSample(<value1> : number) => double</code> Gets the unbiased variance of a column. * ``varianceSample(sales)`` ___ <a name="varianceSampleIf" ></a> ### <code>varianceSampleIf</code> <code>varianceSampleIf(<value1> : boolean, <value2> : number) => double</code> Gets the unbiased variance of a column, based on criteria. * ``varianceSampleIf(region == 'West', sales)`` ___ ## W <a name="weekOfYear" ></a> ### <code>weekOfYear</code> <code>weekOfYear(<value1> : datetime) => integer</code> Gets the week of the year when given a date. * ``weekOfYear(toDate('2008-02-20')) -> 8`` ___ <a name="weeks" ></a> ### <code>weeks</code> <code>weeks(<value1> : integer) => long</code> Gets the duration in milliseconds for the number of weeks. * ``weeks(2) -> 1209600000L`` ___ ## X <a name="xor" ></a> ### <code>xor</code> <code>xor(<value1> : boolean, <value2> : boolean) => boolean</code> Uses the logical `XOR` operator. Same as the `^` operator. * ``xor(true, false) -> true`` * ``xor(true, true) -> false`` * ``true ^ false -> true`` ___ ## Y <a name="year" ></a> ### <code>year</code> <code>year(<value1> : datetime) => integer</code> Gets the year value of a date. * ``year(toDate('2012-8-8')) -> 2012`` ## Related content - List of all [aggregate functions](data-flow-aggregate-functions.md). - List of all [array functions](data-flow-array-functions.md). - List of all [cached lookup functions](data-flow-cached-lookup-functions.md). - List of all [conversion functions](data-flow-conversion-functions.md). - List of all [date and time functions](data-flow-date-time-functions.md). - List of all [expression functions](data-flow-expression-functions.md). - List of all [map functions](data-flow-map-functions.md). - List of all [metafunctions](data-flow-metafunctions.md). - List of all [window functions](data-flow-window-functions.md). - Learn how to use [Expression Builder](concepts-data-flow-expression-builder.md).

Success! Branch created successfully. Create Pull Request on GitHub

Error: