DataChain Formulas

Module HandleData handleData-color.png and GenericsData Logo Generics Data

Datachain enables data to be processed and cross-referenced at various strategic levels in the value chain: Business Entities, DataBlocks and HandleData data sources.
These formulas call functions characterized by a type of input (argument) and output (return) values.

DataChain offers more than 250 unit functions for creating the formulas detailed on this page.

Formula generator

The formula generator allows you to implement a personalized formula using the available functions combined with the processed data and with constants, edited manually or not.

Formula generation screen

EcranFormules

1 List of existing formulas in the working context. Note that the notion of context corresponds to the origin of the call to the formula management dialog box. For example, if the "Formulas" dialog box is requested from a step of a DataBlock, the formulas presented in the list are those of the current step.

2 Button to activate or deactivate a formula. If a formula is disabled then the column is still present in the table of values ​​but the values ​​are set to Null.

3 Icon used to edit the formula.

4 Icon used to duplicate the formula.

5 Icon used to delete the formula.

6 Entry zone for the caption of a formula (Required)

7 Input area used to describe a formula

8 Formula input box

9 Icon for copying the formula

10 Icon for pasting the formula

11 Icon allowing access to the formula catalog

12 Icon used to add the current formula to the formula catalog

13 Allows you to change the display of the formula (tree mode)

14 Button to create a formula

15 Deletes all formulas from the DataBlock (a popup asks for confirmation)

16 Quits the formula entry popup

Entering a formula

information Note that unit functions are organized in packages

  • str. for operations on strings (character strings)

  • num. for operations on numerics

  • date. for operations on dates

  • bool. for operations on booleans

  • url. for operations on urls

  • geo. for geographic operations

  • list. for operations on lists

  • dc.,words., bin., file., http. for DataChain specific functions

  • Entering a formula is done in the 8 area

  • A formula may consist of one or n unit functions.

  • A formula consists of n attributes.

  • An attribute is

    • or a direct entry of information

    • or an available column from the source

    • either an existing formula

    • or another unitary formula

    • or a value chosen from a closed list, proposed by DataChain

  • For some attributes, only a specific type is accepted (example: num.sine(argument) where argument must be of the numeric type (Integer, BigInteger, Decimal, etc.).

  • In output, a formula presents a predefined type of the data it makes available.

  • A formula returns null if a type inconsistency in the arguments is found.

EcranFormules

1 Areaentering a formula

2 Formula search box

3 List of available formulas

information Note that the list of elements may depend on the attribute type. For example if the attribute is of type Integer, then elements of type Integer will be proposed

4 Access to formula catalog

information Note that the formulas proposed in the catalog may depend on the type of attribute. For example if the attribute is of type Integer, then only formulas that are of Integer type will be offered

5 Validate the formula and close the popup

6 Empty formula input box

7 Cancels the current formula

To start entering a formula

  • Click on the New Formula button

  • Enter a label

  • Then click on the zone of choice of a function. The list of unit functions is proposed.

  • Choose a function

  • Complete and specify each argument.

Creation Formulas

The principle is to choose a function, then, by clicking on each attribute, specify the input attributes that will be consumed. display

1 Function name (In the str.split function example, 2 arguments are required)

2 A value to process (In the example, the COMMUNE column).

3 The separator (in the example it is a - )

4 View area "tree of the formula.

5 Zone displaying the arguments expected by the selected formula.

6 When the formula is finished, click on the Validate button

information Note that an Empty button is used to reset all the elements (label, functions, description, etc.) of the formula.

Formula catalog

The formula manager is used to generate standard formulas and integrate them into a catalog. The formula thus integrated can be used later on demand.

When integrating a formula into the catalog from a current formula, the column references of the current Datablock are replaced by a wildcard (/?/). The values ​​of other attributes that do not refer to a column in the current data frame are retained.

information Note that a catalog formula may (or may not) be shared with other users. The creator of the formula can decide at any time the status of this sharing

Formula catalog management

To add a formula to the catalog, select it then click on Catalogue icon

To access the catalog, click on Catalogue icon

Save a formula in the formula catalog

Adding a formula to the catalog

The screen for saving a formula in the catalog allows you to

  • 1 specify a label (Required)

  • 2 specify one or more tags

  • 3 specify a description

  • 4 choose the sharing status of the formula with other users

5 Displays the detail of the formula 6 Save and close the popup 7 Cancel and close the popup

Assign a formula present in the formula catalog

Case 1 - Assign a formula from the catalog when creating a new formula

In the Formula manager popup, click on new formula then on the icon Catalog icon to open the catalog popup formula

Description of the screen for adding a formula to the catalog

  • 1 Select to display only the formulas of the current user

  • 2 Select formula

  • 3 Zone indicating the selected formula

  • 4 Click on Assign to close the popup and return to the formula creation page

  • 5 Close popup without affecting formula

  • 6 User can change theand shares its formulas

  • 7 Remove formula from catalog

Case 2 - Assign a catalog formula to an attribute of a current formula

Formula addition in a formula

In this case, position yourself at the level of the attribute of the current function 1 and click on Icon catalog

Unitary formulas for Decimal type numerics

dec.abs

Objective : Returns the absolute value of a number, i.e. a number without its sign.

  • 1 Argument: Number: data whose absolute value you want to obtain.

Returns: Returns a value of type Decimal number. Returns Null if the value is not a numeric.

dec.abs(-2.23344) RETURNS 2.23344
dec.abs(2.23344) RETURNS 2.23344
dec.abs(MARTIN) RETURNS Null

dec. round

Objective : Rounds a numeric value to the specified number of decimal places.

  • 2 Arguments: Numeric Value: Decimal number to round off Rounding level: Number of digits defining the rounding

Returns: Returns a value of Decimal type Returns null if the argument to round is not numeric Returns null if the Rounding level argument is not an Integer number

dec.round(-2.23344,2) RETURNS -2.23
dec.round(2.23344,3) RETURNS 2.233
dec.round(MARTIN,3) RETURNS Null

dec.is_numeric

Objective : Returns the boolean "True" if the value is numeric, "False" otherwise

  • 1 Argument: Value to process: Value to analyze

Return : Returns a value of Boolean type

dec.is_numeric(123) RETURNS True
dec.is_numeric(12.3) RETURNS True
dec.is_numeric(1212.3) RETURNS True
dec.is_numeric(1112.3) RETURNS False
dec.is_numeric(12/01/2018) RETURNS False
dec.is_numeric(12,4) RETURNS False
dec.is_numeric(TOTO) RETURNS False

dec.cosine

Objective : Returns the Cosine of a decimal value in radians

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.cosine(12) RETURNS 0.8438539587324921

dec.cosine_invers(arc Cosine)

Objective : Returns the value of the inverse of the Cosine (ArcCosine) of a decimal value in radians

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.cosine_invers(0.9) RETURNS. 0.4510268118

dec.cosine_hyperb

Objective : Returns the Hyperbolic Cosine of a decimal value in radians

  • 1 Argument: Decimal value to process

Returns: Returns a value of decimal type

dec.cosine_hyperb(0.9) RETURNS 1.4330863854487745

dec.nan_to_null

Objective : Convert NaN values ​​to null

  • 1 Argument: Decimal type column to process

Return : Returns null

dec.nan_to_null(column) RETURNS null

dec.sine

Objective : Returns the Sine of a decimal value in radians

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.sine(0.9) RETURNS 0.7833269096274834

dec.sine_invers (sine arc)

Objective : Returns the value of the inverse of the Sine (ArcSine) of a decimal value in radians

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.sine_invers(0.9) RETURNS 1.1197695149986342

dec.sine_hyperb

Objective : Returns the Hyperbolic Sine of a decimal value in radians

  • 1 Argument: Decimal value to process

Returns: Returns a value of decimal type

dec.sinus_hyperb(0.9) RETURNS 1.0265167257081753

dec.tangent

Objective : Returns the tangent of a decimal value

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.tangent(0.9) RETURNS 1.2601582175503392

dec.tangent_invert

Objective : Returns the value of the inverse of the tangent (ArcTangent) of a decimal value

  • 1 Argument: Decimal value to process

Returns: Returns a value of Decimal type

dec.tangent_invert(0.9)RETURNS 0.7328151017865066

dec.tangent_hyperb

Objective : Returns the Hyperbolic Tangent of a decimal value

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.tangent_hyperb(0.9) RETURNS 0.7162978701990245

dec.degree_to_rad

Objective : Transforms a value expressed in degrees into a value expressed in radians

  • 1 Argument: Decimal value to process

Return : Returns a value of Decimal type

dec.degree_to_rad(0.9) RETURNS 0.015707963267948967

dec.rad_to_degree

Objective : Transforms a value expressed in radians into a value expressed in degrees

  • 1 Argument:

Numerical value to process

Return : Returns a value of decimal type

dec.rad_to_degree(0.9) RETURNS 51.56620156177409

dec.exponential

Objective : Returns the Exponential of a decimal value

  • 1 Argument:

Decimal value to process

Return : Returns a value of decimal type

dec.exponential(0.23) RETURNS 1.2586000099294778

dec.factorial

Objective : Returns the Factorial of a decimal value

  • 1 Argument: Decimal value to process

Return : Returns a value of decimal type

dec.factorial(15) RETURNS 1307674368000

dec.hex

Objective : Returns the Hexadecimal value of a decimal value

  • 1 Argument:

Decimal value to process

Return : Returns a value of type string

dec.hex(123.34) RETURNS 7B

dec.log

Objective : Returns the Népérien Logarithm of a decimal value

  • 1 Argument:

Decimal value to process

Returns: Returns a value of decimal type

dec.log(0.23) RETURNS -1.4696759700589417

dec.log_10

Objective : Returns the logarithm to base 10 of a decimal value

  • 1 Argument:

Decimal value to process

Return : Returns a value of decimal type

dec.log_10(0.23) RETURNS -0.638272163982407

dec.log2

Objective : Computes the base 2 LOG of a numeric value

  • 1 Argument:

Decimal value to process

Return : Returns a value of decimal type

dec.log_2(0.23) RETURNS -2.1202942337177118

dec.square

Objective : Returns the square root of a numeric value

  • 1 Argument:

Numerical value to process

Return : Returns a value of decimal type

dec.square(12) RETURNS 3.4641016151377544

dec.cube

Objective : Returns the cubic root of a numeric value

  • 1 Argument:

Numerical value to process

Return : Returns a value of decimal type

dec.cube(12) RETURNS 2.2894284851066637

dec. sign

Objective : Returns the value 0 if the value to analyze is equal to 0, 1.0 if the value is greater than 0, -1.0 if the value is less than 0

  • 1 Argument:

Numerical value to analyze

Return : Returns a value of decimal type

dec.sign(-1245.2) RETURNS -1
dec.sign(1245.2) RETURNS 1
dec.sign(Null) RETURNS Null

dec.to_int

Objective : Transforms a decimal type value into an integer type value

  • 1 Argument:

Numerical value to process

Return : Returns a value of type integer

dec.to_bigint

Objective : Transforms a decimal type value into a BigInteger type value

  • 1 Argument:

Decimal value to process

Return : Returns a value of type BigInteger

dec.operator

Objective : Numerical operations on a list of values ​​(Sum, Product, division, subtraction, average, minimum, maximum)

  • n Arguments:

Operator: choice of the operator in a list (sum, product, division, difference, average, minimum, maximum) List of values: specifies the list of values on which the operator will be applied

Return : Returns a value of decimal numeric type

dec.operator(sum, ARGS(2, 3, 4, 2)) RETURNS 11
dec.operator(difference, ARGS(2, 3, 4, 2)) RETURNS -7

dec.fix

Objective : Returns the integer part of a Decimal number

  • 1 Argument: Decimal number to process

Return : Returns a value of type integer

dec.fix(123.3) RETURNS 123

dec.replace_null

Objective : Replaces the null value of a numeric type cell with a specified value

  • 2 Arguments:

Value 1: Character string to process Value 2: Default value if value 1 is null

Return : Returns a value of Decimal type

dec.replace_null(Null, -1) RETURNS -1
dec.replace_null(234.3, -1) RETURNS 234.3

dec.rate_variation

Objective : Returns a rate of change

  • 4 Arguments:

Initial decimal value: First value Final decimal value: Second value nb of decimal: Number of decimals after the comma Result format: Ratio or percentage

Return : Returns a value of decimal

dec.to_str

Objective : Transforms a decimal type value into a String type value

  • 1 Argument:

Decimal value to process

Return : Returns a value of type String

dec.generate

Objective: Generation of a list of decimals

3 arguments:

Value 1: Increment: Value of the Decimal type increment Value 2: Start value: Decimal Value 3: End value: Decimal

Return: Returns a list of Decimal

dec.generate(0.5,4, 6) RETURNS [4, 4.5, 5, 5.5, 6]

dec.list.contains

Objective : Tests the presence in the list of a decimal

2 arguments:

Value 1: List of decimals (Type List) Value 2: Decimal value to search for

Return: Returns a boolean True or False

dec.list.contains([12.23,23,3,2.1,25], 12.23) RETURNS True
dec.list.contains([12.23,23,3,2.1,25], 17.31) RETURNS False

dec.list.create

Objective : Realizes the creation of a list of decimals from a character string

1 argument:

Value 1: Character string of string type

Return: Returns a decimal type list

dec.list.create([12.23,23,3,2.1,25]) RETURNS [12.23,23,3,2.1,25]

dec.list.min

Objective : Returns the smallest decimal value in a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a decimal value

dec.list.min([12.23,23,3,2.1,25]) RETURNS 2.1

dec.list.max

Objective : Returns the largest decimal value in a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a decimal value

dec.list.max([12.23,23,3,2.1,25]) RETURNS 25

dec.list.median

Objective: Returns the median of a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a decimal value

dec.list.median([123.34,1234.23,123.05,-123.05] ) RETURNS 123.195

dec.list_mean

Objective : Returns the average of a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a decimal value

dec.list.mean([123.34,1234.23,123.05,-123.05] ) RETURNS 339.3925

dec.list.percentile

Objective : Returns the percentile of a list of decimals

2 arguments:

Value 1: List of decimals (List type) Value 2: Calculated percentile

Return: Returns a decimal value

dec.list.percentile([123.34,1234.23,123.05,-123.05, 50] ) RETURNS 678.785

dec.list.sum

Objective : Returns the sum of a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a decimal value

dec.list.sum([123.34,1234.23,123.05,-123.05] ) RETURNS 1357.57

dec.list.sort

Objective : Returns a list of decimals sorted ascending

1 argument:

Value 1: List of decimals (List type)

Return: Returns a list of decimals

dec.list.sort([12.23,23,3,2.1,25]) RETURNS [2.1, 3, 12.23, 23, 25]

dec.list.get

Objective : Returns a decimal value included in a list according to its index in the list

2 arguments:

Value 1: List of decimals (List type) Value 2: Index (integer type)

Return: Returns a decimal

dec.list.get([123.34,1234.23,123.05,-123.05,,1] ) RETURNS 1234.23

dec.list.intersect

Objective : Returns a list of decimal values ​​in common between two Decimal type lists

2 arguments:

Value 1: Lists of decimals (List type) Value 2: Lists of decimals (List type)

Return: Returns a list of decimals

dec.list.union

Objective :Returns a list containing all the decimal values ​​of the two Decimal-type lists

2 arguments:

Value 1: Lists of decimals (List type) Value 2: Lists of decimals (List type)

Return: Returns a list of decimals

dec.list.diff

Objective : Returns a list containing all the different decimal values ​​between two Decimal-type lists

2 arguments:

Value 1: Lists of decimals (List type) Value 2: Lists of decimals (List type)

Return: Returns a list of decimals

dec.list.max_occur

Objective : Returns the most frequent decimal (or decimals) in a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a List of decimals type

dec.list.min_occur

Objective : Returns the least frequent decimal (or decimals) in a list of decimals

1 argument:

Value 1: List of decimals (List type)

Return: Returns a List of decimals type

dec.list.as_str

Objective : Transforms a list of decimals into a character string separated by the comma character,

1 argument:

Value 1: List of decimals (List type)

Return: Returns a String

Unit formulas for numerics of Integer type

int.to_dec

Objective : Transforms an integer type value into a decimal type value

  • 1 Argument:

Numerical value to process

Return : Returns a value of decimal type

int.to_bigint

Objective : Transforms a value of type integer into a value of type BigInteger

  • 1 Argument:

_Integer value to process

Return : Returns a value of type BigInteger

int.replace_null

Objective : Replaces the Null value of an Integer numeric type cell with a specified value

  • 2 Arguments:

Value 1: Character string to process Value 2: Default value if value 1 is null

Return : Returns a value of type integer

int.replace_null(Null, -1) RETURNS -1
int.replace_null(2343, -1) RETURNS 2343

int.is_odd

Objective : Returns the boolean "True" if the value is odd, "False" otherwise

  • 1 Argument: Value to process: Value to analyze

Return : Returns a value of Boolean type

int.is_odd(123) RETURNS True
int.is_odd(100) RETURNS False
int.is_odd(TOTO) RETURNS False

int.is_even

Objective : Returns the boolean "True" if the value is even, "False" otherwise

  • 1 Argument: Value to process: Value to analyze

Return : Returns a value of Boolean type

int.id_even(123) RETURNS False
int.id_even(100) RETURNS True
int.id_even(TOTO) RETURNS False

int.list.get

Objective : Returns an Integer value included in a list according to its index in the list

2 arguments:

Value 1: List of integer numbers (List type) Value 2: Index (integer type)

Return: Returns an integer

int.list.get([123,1234,124,-125,, 1] ) RETURNS 1234

int.list.sort

Objective : Performs the ascending sort of a list of integer numbers

1 argument:

Value 1: List of integer numbers (List type)

Return: Returns a list of integer numbers

int.list.sort([12,23,3,2,25]) RETURNS [2, 3, 12, 23, 25]

int.list.create

Objective : Realizes the creation of a list of integer numbers from a sequence of integers

1 argument:

Value 1: Integer number sequence

Return: Returns a list of type integer

int.list.create([12,23,3,2,25]) RETURNS [12,23,3,2,25]

int.list.intersect

Objective : Returns a list of Integer values ​​in common between two lists of type Intger

2 arguments:

Value 1: Lists of Integer (List type) Value 2: Lists of Integer (List type)

Return: Returns a list of Integers

int.list.union

Objective : Returns a list containing the set of Integer values ​​of the two Integer type lists

2 arguments:

Value 1: Lists of Integer (List type) Value 2: Lists of Integer (List type)

Return: Returns a list of Integers

int.list.diff

Objective : Returns a list containing all the different Integer values ​​between two Integer type lists

2 arguments:

Value 1: Lists of Integer (List type) Value 2: Intege Listsr (List type)

Return: Returns a list of Integers

int.to_str

Objective : Transforms an Integer type value into a String type value

  • 1 Argument:

Integer value to process

Return : Returns a value of type String

int.to_date

Objective : Transforms an Integer type value into a Date type value

  • 1 Argument:

Integer value to process

Return : Returns a value of type date

int.to_bool

Objective : Transforms an Integer type value into a Boolean type value

  • 1 Argument:

Integer value to process

Return : Returns a value of Boolean type

int.list.max

Objective : Returns the largest integer number in a list of integers

1 argument:

Value 1: List of integer numbers

Return: Returns a type integer value

int.list.min

Objective : Returns the smallest integer number in a list of integers

1 argument:

Value 1: List of integer numbers

Return: Returns a type integer value

int.list.max_occur

Objective : Returns the most frequent Integer number (or Integer numbers) in a list of Integer numbers

1 argument:

Value 1: List of Integer numbers (List type)

Returns: Returns a List of numbers Integer type

int.list.min_occur

Objective : Returns the least frequent Integer number (or Integer numbers) in a list of Integer numbers

1 argument:

Value 1: List of Integer numbers (List type)

Returns: Returns a List of numbers Integer type

int.list.as_str

Objective : Transforms a list of Integer into a String of characters separated by the comma character,

1 argument:

Value 1: List of Integer (List type)

Return: Returns a String

Unitary formulas for numerics of type BigInteger

bigint.replace_null

Objective : Replaces the Null value of a BigInteger numeric type cell with a specified value

  • 2 Arguments:

Value 1: BigInteger to process Value 2: Default value if value 1 is null

Return : Returns a value of type BigInteger

bigint.replace_null(Null, -1) RETURNS -1
bigint.replace_null(45678456776545678765, -1) RETURNS 45678456776545678765

bigint.to_int

Objective : Transforms a value of type BigInteger into a value of type integer

  • 1 Argument:

BigInteger value to process

Return : Returns a value of type integer

bigint.to_dec

Objective : Transforms a BigInteger type value into a decimal type value

  • 1 Argument:

BigInteger value to process

Return : Returns a value of decimal type

bigint.to_str

Objective : Transforms a value of type BigInteger into a value of type string

  • 1 Argument:

BigInt value to process

Return : Returns a value of type string

bigint.to_date

Objective : Transforms a BigInt type value into a Date type value

  • 1 Argument:

BigInteger value to process

Return : Returns a value of Date type

bigint.to_bool

Objective : Transforms a BigInteger type value into a Boolean type value

  • 1 Argument:

BigInteger value to process

Return : Returns a value of Boolean type

bigint.list.sort

Objective : Returns a list of BigIntegers sorted ascendingly

1 argument:

Value 1: List of BigInteger (List type)

Return: Returns a list of BigIntegers

bigint.list.sort([12.23,23,3,2.1,25]) RETURNS [2.1, 3, 12.23, 23, 25]

bigint.list.create

Objective : Realizes the creation of a BigInteger list from

1 argument:

Value 1: Sequence of BigInteger

Return: Returns a list of type BigInteger

bigint.list.create([3456789,4567890]) RETURNS [3456789,4567890]

bigint.list.max

Objective : Returns the largest BigInteger number in a list of BigIntegers

1 argument:

Value 1: List of BigInteger numbers

Return: Returns a value type BigInteger

bigint.list.min

Objective : Returns the smallest BigInteger number in a list of BigIntegers

1 argument:

Value 1: List of BigInteger numbers

Return: Returns a value type BigInteger

bigint.list.contains

Objective : Tests the presence in the list of a BigInteger

2 arguments:

Value 1: List of BigInteger (Type List) Value 2: BigInteger value to search for

Return: Returns a boolean True or False

bigint.list.intersect

Objective : Returns a list of BigInteger values ​​in common between two lists of type BigInteger

2 arguments:

Value 1: Lists of BigInteger (List type) Value 2: Lists of BigInteger (List type)

Return: Returns a list of BigInteger

bigint.list.union

Objective : Returns a list containing the set of BigInteger values ​​of the two lists of type BigInteger

2 arguments:

Value 1: Lists of BigInteger (List type) Value 2: Lists of BigInteger (List type)

Return: Returns a list of BigIntegers

bigint.list.diff

Objective : Returns a list containing all the different BigInteger values ​​between two lists of type BigInteger

2 arguments:

Value 1: Lists of BigInteger (List type) Value 2: Lists of BigInteger (List type)

Return: Returns a list of BigIntegers

bigint.list.get

Objective : Returns a BigInteger value included in a list given its index in the list

2 arguments:

Value 1: List of BigInteger (List type) Value 2: Index (integer type)

Return: Returns a BigInteger

bigint.list.get([23456,34567,34567656,65567876678765,6567898765678765] ) RETURNS 34567

bigint.list.max_occur

Objective : Returns the most frequent BigInteger number (or BigInteger numbers) in a list of BigInteger numbers

1 argument:

Value 1: List of BigInteger numbers (List type)

Returns: Returns a List of BigInteger number type

bigint.list.min_occur

Objective : Returns the least frequent BigInteger number (or BigInteger numbers) in a list of BigInteger numbers

1 argument:

Value 1: List of BigInteger numbers (List type)

Returns: Returns a List of BigInteger number type

bigint.list.as_str

Objective : Transforms a list of BigInteger into a String of characters separated by the comma character,

1 argument:

Value 1: List of BigInteger (List type)

Return: Returns a String

Unitary formulas for the Character string type

str.transform_to_num

Objective : Transforms a character string into a valid decimal number. Removes non-numeric characters and sets the correct decimal separator character.

  • 2 Arguments:

Value 1: Character string to process (String type) Value 2: Decimal character >> . Where ,

Return : Returns a value of decimal

str.transform_to_num(1 2342.2, . ) RETURNS 12342.2

str.if_absent

Objective : Adds a prefix or a suffix to a character string in case it is absent

  • 3 Arguments:

Value to process: Character string to process Suffix to add: Choice of the type of increment contained in the following list: List of suffixes to search for: List of characters to search for

Return : Returns a value of type String

str.if_absent(Brand,'-NEW',ARGS('-ROMEO')): Add "-NEW" if "ROMEO" is absent
str.if_absent(ALPHA-ROMEO,'-NEW',ARGS('-ROMEO')) RETURNS ALPHA-ROMEO
str.if_absent(CITROEN,'-NEW',ARGS('-ROMEO')) RETURNS CITROEN-NEW
str.if_absent(RENAULT,'-NEW',ARGS('-ROMEO')) RETURNS RENAULT-NEW

str.find

Objective : Search if AT LEAST one value (or at least one value from a list of values) is present in a character string

  • 2 Arguments:

Value to process: Character string to process List of values ​​to search for: ARGS (character 1, character 2, character n)

Return : Returns a value of Boolean type

str.find(VOLVO, ARGS(V,E)) RETURNS True
str.find(VOLVO, ARGS(X,E)) RETURNS False

str.find_case

Objective : Check for consistency of case

  • 3 Arguments:

Value to process: Character string to process (String type) Casse: Specifies the case Uppercase or Lowercase Type of search: Specifies whether the case to be checked is total or partial

Return : Returns a value of Boolean type Returns Null value if the type of case is not present in the proposed list

str.find_case(VOLVO, Lowercase,All) RETURNS False
str.find_case(VOLVO, Shift,All) RETURNS TrueTIP: str.find_case(VOLVO, Uppercase,partial) RETURNS False
str.find_case(VOLVO-10, Shift, All) RETURNS False

str.encrypt

Objective : Number a string of characters for anonymization

  • 1 Argument:

Value to process: Character string to process (String type)

Return : Returns a value of type String

str.encrypt(159 2.0 JTDm (136ch)) RETURNS 49342454756235006f16dd175d76f9aa

str.complete

Objective : Allows you to complete a value with characters

  • 4 Arguments:

Value to process: Value to process Character to complete: Characters used to complete the value to be processed up to the defined length Final length: Final length of the string once completed by the characters Type of complement: Direction of the complement (Left or Right)

Return : Returns a value of type String

str.complete(50, 0, 10, Left) RETURNS 0000000050
str.complete(2350, 0, 10, Left) RETURNS 0000002350
str.complete(1000002350, 0, 10, Left) RETURNS 1000002350
str.complete(ABCD, -, 10, Right) RETURNS ABCD------

str.concat

Objective : Concatenates a set of values

  • n Arguments: Val 1 to Val n: Set of values to concatenate

Return : Returns a value of type String

str.concat(ARGS(it,looks,beautiful) RETURNS it looks beautiful
str.concat(ARGS(it, ,is, ,beautiful) RETURNS it is beautiful

str.contains_only

Objective : Checks if ALL the specified characters are present in a processed character string

  • 2 Arguments: Value 1: Value to process Value 2: Searched characters

Return : Returns a value of Boolean type

str.contains_only(AUDI, IUDA) RETURNS True
str.contains_only(AUDI, I UDA) RETURNS False
str.contains_only(AUDI, IUD) RETURNS False

Attention, the Space character is considered as a character.

str.diff

Objective : Compare two strings by returning the part where they differ

  • 2 Arguments: Value 1: First value Value 2: Second value

Return : Returns a value of type String

str.diff(VOLVO, VOLCO) RETURNS CO
str.diff(CAR, VALET) RETURNS IER

str.framing

Objective : Allows you to frame a value with one or more characters on the left and on the right

  • 2 Arguments: Value 1: Value to process Value 2: Character or group of characters that will frame the value to process

Return : Returns a value of type String

str.framing(MyValue, @) RETURNS @MyValue@

str.framing_if_absent

Objective : Surrounds a value with one or more characters on the left and on the right only if this value is missing

  • 2 Arguments:

Value 1: Value to process Value 2: Character or group of characters that will frame the value to process

Return : Returns a value of type String

str.framing_if_absent(MyValue, @) RETURNS @MyValue@
str.framing_if_absent(@MyValue, @) RETURNS @MyValue@

str.remove

Objective : Remove one or a character group from a character string

  • 2 Arguments:

Value 1: Value to process Value 2: Character or group of characters to remove

Return : Returns a value of type String

str.remove(VOLVO, O) RETURNS VLV
str.remove(MARTIN, AR) RETURNS MTIN

str.remove_extend

Objective : Removes one or more characters from a string

  • 2 Arguments:

Value 1: Value to process Value 2: Character or list of characters to remove

Return : Returns a value of type String

str.remove_extend(VOLVO, O) RETURNS VLV
str.remove_extend("the dog /left: he came back yesterday", ARG(;,:,/)) RETURNS "the dog left: he came back yesterday"

str.remove_start

Objective : Remove a substring only if it is at the beginning of a source string, otherwise return the source string

  • 2 Arguments:

Value 1: Value to process Value 2: Character or group of characters to remove if it is at the beginning of the value to process

Return : Returns a value of type String

str.remove_start(VOLVO, VOL) ​​RETURNS VO
str.remove_start(MARTIN, VOL) ​​RETURNS MARTIN

str.remove_linebreak

Objective : Delete the newline if it is present at the end of the character string

  • 1 Argument: Value 1: Value to process

Return : Returns a value of type String

str.remove_linebreak(VOLVO) RETURNS VOLVO

str.extract

Objective : Extracts a number of characters to the right or left of a value to process

  • 3 Arguments:

Value to process: Value on which the extraction must be performed Direction of extraction: Direction of extraction to right or left Nb of characters to extract: Integer number allowing to indicate the number of characters to extract

Return : Returns a value of type String

str.extract(ST-ETIENNE, left, 3) RETURNS ST-
str.extract(ST-ETIENNE, right, 3) RETURNS NNE

str.extract_middle

Objective : Extracts a number of characters from the middle of a string

  • 3 Arguments:

Value to process: Value on which the extraction must be performed Position of the start of the extraction: Position of the start of the extraction in the value Nb of characters to extract: Integer number allowing to indicate the number of characters to extract from the starting position

Return : Returns a value of type String

str.extract_middle(ST-ETIENNE, 3, 3) RETURNS ETI

str.extract_by_str

Objective : Extracts one of the characters in the middle of a character string with respect to the position of a reference character

  • 3 Arguments: Value to process: Value on which the extraction must be performed Character to find: Determines the reference character to find the position of the extraction _Direction of extraction: Right or Left

Return : Returns a value of type String

str.extract_by_str(ST ETIENNE, T, Left) RETURNS S
str.extract_by_str(ST ETIENNE, T, Right) RETURNS IENN

str.extract_by_index

Objective : Returns the extraction of a group of characters by specifying the start position and the end position in the character string.

  • 3 Arguments:

Value 1: Character string to process Value 2: Start position (integer) Value 3: End position (integer)

Return : Returns a value of type String

str.extract_by_index(MY DOG, 4, 6) RETURNS CH

str.extract_by_regex

Objective : Extracts a character or a group of characters from a string using a REGEX regular expression

  • 2 Arguments:

Value to process: Value on which the extraction must be performed Characters to extract: Regular Expression specifying the character(s) to extract.

Return : Returns a value of type Object String

str.extract_by_regex(ANDRE, \R) RETURNS ["R"]

str.frequency

Objective : Returns the frequency of a character or a group of characters in a string

  • 2 Arguments:

Value to process: Value on which the count must be performed Character to search for: Character(s) to count

Return : Returns a value of type integer

str.frequency(ST ETIENNE, E) RETURNS 3
str.frequency(ST ETIENNE, T) RETURNS 2

str.abbrev

Objective : Build an abbreviation from a string

  • 3 Arguments: Value to process: Represents the value to process Start: Position of the value to be processed which will constitute the start of the abbreviation. Must be greater than or equal to 4 Length: Total length of the abbreviation returned (including the "…" systematically added at the beginning and at the end) Must be greater than or equal to 4

Return : Returns a value of character string or JsonString type

str.abrev(Dog has been away for 3 days, 5,10) RETURNS …​well…​
str.abrev(The dog has been gone for 3 days, 5, 20) RESULT …​it’s gone…​

str.capitalize

Objective : Converts the first character of a character string value to uppercase

  • 1 Argument: Value to process: Represents the value to process

Return : Returns a value of character string type

str.capitalize(stone) RETURNS Stone

str.list.join

Objective : Unions all elements of a list into a string, using a given separator

List to process: Represents the list of stringscharacter to deal with Separator: Specifies the separator that will be used to separate character strings

Return : Returns a value of character string type

str.invert

Objective : Returns the inverse of a value of type character string

  • 1 Argument: Value to process: Represents the value to process

Return : Returns a value of character string type

str.invers(MARTIN) RETURNS NITRAM

str.join

Objective : Attaches a list of values with a specific character

  • 2 Arguments:

Value 1 to Value n: List of values ​​to join Join character: Character joining the values ​​of the list

Return : Returns a value of character string type

str.join(ARGS(UN, DE, 3, QU, 5, 6), @) RETURNS UN@DE@3@QU@5@6

str.size

Objective : Gives the length of a character string

  • 1 Argument:

Value 1: Character string to process

Return : Returns a value of type integer

str.size(MARTIN) RETURNS 6

str.not_contains

Objective : Returns True if the character sought is not in the parsed value

  • 2 Arguments:

Value 1: Value to analyze Value 2: Character to search for

Return : Returns a value of Boolean type

str.not_contains(AUDI,D) RETURNS False
str.not_contains(VOLVO,D) RETURNS True

str.remove_space

Objective : Removes all or part of the blank characters in a character string

  • 2 Arguments:

Value 1: Character string to process Type: Type of action on whitespace characters. To left, To right, All blanks, Keep only one blank between each word

Return : Returns a value of character string type

str.remove.space (The dog is gone, keep only a blank between each word) RETURNS The dog is gone
str.remove.space ( The dog is gone , Left) RETURNS The dog is gone
str.remove.space ( The dog is gone , Right) RETURNS The dog is gone
str.remove.space ( The dog is gone , All benches) RETURNS The dog is gone

str.diff_position

Objective : Returns the position of the first difference between two values ​​of type character string starting from left to right

  • 2 Arguments:

Value 1: Value 1 to process Value 2: Value 2 to process

Return : Returns a value of type integer

str.diff_position(MALINE, MALINOI) RETURNS 5

str.replace

Objective : Replaces characters with other characters in a character string value

  • 3 Arguments:

Value: Value to process Character to search for: Characters to search for in the value to process Character to replace: Characters to replace in the value to process

Return : Returns a value of type String

str.replace(ST ETIENNE, ST , SAINT) RETURNS SAINT ETIENNE

str.replace_regex

Objective : Replaces characters according to a regular expression with other characters in a value of type Object-string-of-characters

  • 3 Arguments:

Value: Value to process Character to search for: Regular Expression specifying the character(s) to search for. Character to replace: Replacement characters

Return : Returns a value of type String

str.replace_regex(1234.23, \\., ,) RETURNS 1234.23

str.replace_null

Objective : Replaces the Null value of a character string cell with a specified value

  • 2 Arguments:

Value 1: Character string to process Value 2: Default value if value 1 is null

Return : Returns a value of type String

str.replace_null(Null, Not Provided) RETURNS Not Provided
str.replace_null(BIKE, -1) RETURNS BIKE

str.remove_accent

Objective: Replace the accented characters of a character string with unaccented characters.

Remove accented characters from a string

  • 1 Argument: Value: Value to process

Return : Returns a value of character string type

str.remove_accent(coracoid manner) RETURNS coracoid manner

str.split_from_regex

Objective : Performs a split of a character string via a REGEX regular expression

  • 2 Arguments: Value :Value to process Character to search for: Regular Expression specifying the rule for dividing the value to be processed

Return : Returns a value of type Object-string-of-characters

Splitting a character string according to the presence of the letter R TIP: str.split_from_regex(MARY, \A) RETURNS ["M","RIE"]

str.split

Objective : Explodes a character string by specifying a separator.

2 arguments:

Value 1: Character string to process Value 2: Character used to perform the splitting

Return: Generates a list of strings

str.split(“hdh_login=TEST ;hdh_token=RRYYTTTY, hdh_id=222222”, ;) RETURNS [“hdh_login=TEST”, “hdh_token=RRYYTTTY”,,”hdh_id=222222”]

str.split_and_concat

str.create

Objective : Allows you to enter a character string type value

  • 1 Argument: Value 1: Entered value

Return : Returns a value of character string type

str.create("Hello") RETURNS Hello

str.to_object

Objective : Constructs an object from a key and a value

  • 2 Arguments: Key to process: Characters of the key Value to process: Value characters

Return : Returns an object of type string

str.to_object(A;123.34) RETURNS {"A":"123.34"}

str.to_bigint

Objective : Transforms a value of type character string into a value of type BigInteger

  • 1 Argument:

Value 1: Character string type value to process

Return : Returns a value of type BigInteger

str.to_bigint("5676567865678657865765") RETURNS 5676567865678657865765

str.to_date

Objective : Transforms a character string type value into a Date type value

  • 2 Arguments:

Value 1: Character string type value to process Mask: Specifies the reading format of the value to be processed

Return : Returns a value of Date type

str.to_date("12/01/2012", dd/mm/yyyy) RETURNS 12/01/2012

str.to_dec

Objective : Transforms a character string into a decimal type value

  • 2 Arguments:

Value 1: Character string to be processed of type character string Masque: Specifies the format for reading the character string to be processed

Return : Returns a value of decimal type

str.to_dec("12.21",.) RETURNS 12.21
str.to_dec("12",.) RETURNS 12.0

str.to_integer

Objective : Transforms a character string into an Integer type value

  • 1 Argument: Value 1: Character string type value to process

Return : Returns a value of Integer type

str.to_integer("12") RETURNS 12

str.to_list

Objective : Transforms a string into a list of strings.

  • 1 Argument:

Value 1: Character string to process

Return : Returns a value of type List of character strings

str.to_list(["45","12","12","14","23"]) RETURNS ["45","12","12","14","23 "]

str.to_list_by_step

Objective : Returns a list of values ​​by splitting a string by a fixed split value.

  • 2 Arguments:

Value 1: Character string to process Value 2: Value of the split in number of characters

Return : Returns a value of type List of character string

str.to_list_by_step('K210J230M234L203I103, 4) RETURNS ["K210","J230","M234","L203","I103"]

str.to_words

Objective : Performs the transformation of a character string into a Words type object

  • 1 Argument:

Value 1: Character string to transform

Return : Returns a value of Words Object type

str.to_words(It’s sunny today. That’s fine.) RETURNS a Words Object usable by the semantic rules HandleData and GenericsData See Semantic management documentation)

str.transform_case

Objective : Transform into Lowercase or Uppercase a character string value

  • 2 Arguments:

Value: Value to process Treatment to apply

Return : Returns a value of character string type

str.transform_case(transform test, Uppercase) RETURNS TRANSFORM TEST

str.extract_num

Objective : Extract from a value only the digits

  • 2 Arguments: Value: Value to process Treatment to apply

Return : Returns a value of character string type*TIP: *str.extract_num(BRERA 1750 TBI (200ch)) RETURNS 1750200

str.extract_num(1,232.2 ) RETURNS 2322
str.extract_num(A) RETURNS Empty
str.extract_num(1 451.2) RETURNS 14512
str.extract_num(22,1) RETURNS 221
str.extract_num(2) RETURNS 1
str.extract_num(23.2) RETURNS 232

str.position

Objective : Returns the position of a character or a group of characters in a character string by specifying a start position.

  • 3 Arguments:

Value 1: Character string to process Value 2: Character string to search for Value 3: Search start position

Return : Returns a value of Integer type

str.position(MULT@IMULET, UL, 1) RETURNS 1
str.position(MULT@IMULET, UL, 6) RETURNS 7

str.levenshtein

Objective : Similarity - Returns the value of the LEVENSHTEIN similarity test. The Levenshtein distance is a distance, in the mathematical sense of the term, giving a measure of the difference between two character strings. It is equal to the minimum number of characters that must be deleted, inserted or replaced to go from one character string to another.

  • 1 Argument: Value: Character string to process

Return : Returns a value of Integer type

str.lenvenshtein(REYMOND, REMOND) RETURNS 1

str.soundex

Objective : Returns the SOUNDEX of a character string

  • 1 Argument: Value to process: Represents the value to process

Return : Returns a value of character string type

str.soundex(ALPHA-ROMEO) RETURNS A416

str.extract_key_value

Objective : Performs the extraction from a list of key-value, from a value of a key

4 arguments

Value 1: string to process Value 2: Character separating key-value pair Value 3: Character separating key and value Value 4: Null or specify a key Value 5: Closed list choice between >>>> none, key or value.

Return: Generates a value of type character string

str.extract_key_value(a!1;b!2;c!3,;,!,null(),none) RETURNS {a:1,b:2,c:3}
str.extract_key_value(a!1;b!2;c!3,;,!,'b',value) RETURNS 2
str.extract_key_value(a!1;b!2;c!3,;,!,'2',key) RETURNS b

str.to_bool

Objective : Transforms a string type value into a Boolean type value

  • 1 Argument:

String value to process

Return : Returns a value of Boolean type

str.list.contains

Objective : Tests the presence in a list of a character string

2 arguments:

Value 1: List of character strings (Type List) Value 2: Character string value to search for

Return: Returns a boolean True or False

str.list.contains(["Pierre", "André", "Paul"], "Pierre") RETURNS True
str.list.contains(["Pierre", "André", "Paul"], "Marcel") RETURNS False

str.list.create

Objective : Realizes the creation of a list of character strings from a character string

1 argument:

Value 1: Character string of character string type

Return: Returns a list of type string

str.list.create([Peter, Andrew, Paul] RETURNS ["Peter", "Andre", "Paul"]

str.list.max

Objective: Returns the largest character string in ASCII value in a list of character strings

1 argument:

Value 1: List of String type characters

Return: Returns a type string value

str.list.max([Pierre, André, Paul] RETURNS ["Pierre", "André", "Paul"]

str.list.min

Objective: Returns the smallest character string in ASCII value in a list of character strings

1 argument:

Value 1: List of String type characters

Return: Returns a type string value

str.list.sort

Objective : Performs the ascending sort of a list of character strings

1 argument:

Value 1: List of character strings of type String

Return: Returns a list of type string

str.list.sort(["Peter", "Andre", "Paul"]) RETURNS ["Andre", "Paul", "Peter"]

str.list.get

Objective : Returns a character string included in a list according to its index in the list

2 arguments:

Value 1:Character string list (List type) Value 2: Index (Integer type)

Return: Returns a character string

str.list.get(["Pierre", "André", "Paul"], 2) RETURNS "Paul"

str.list.intersect

Objective : Returns a list of String values ​​in common between two lists of type String

2 arguments:

Value 1: Lists of String (List type) Value 2: Lists of String (List type)

Return: Returns a list of String

str.list.union

Objective : Returns a list containing the set of String values ​​of the two lists of type String

2 arguments:

Value 1: Lists of String (List type) Value 2: Lists of Integer (List type)

Return: Returns a list of String

str.list.diff

Objective : Returns a list containing all the different String values ​​between two lists of type String

2 arguments:

Value 1: Lists of String (List type) Value 2: Lists of String (List type)

Return: Returns a list of String

str.list_exist_str

Objective : Search in a character string for the existence or non-existence of a list of characters

3 arguments:

Value 1: Character string to analyze (Type String) Value 2: Character string list (Type List) Value 3: allows you to define the type of search either Exists or Does not exist

Return: Boolean

str.list.as_str

Objective : Transforms a list of character strings into a character string separated by the comma character,

1 argument:

Value 1: Character string list (List type)

Return: Returns a String

str.list.max_occur

Objective : Returns the most frequent character string (or character strings) in a list of character strings

1 argument:

Value 1: Character string list (List type)

Return: Returns a List type of Character String

str.list.min_occur

Objective :

Returns the least frequent string (or strings) in a list of strings

1 argument:

Value 1: Character string list (List type)

Return: Returns a List type of Character String

Unit formulas for the Date type

date.to_bigint

Objective: Transform* a date type value into a BigInteger type value

1 Argument:

Value 1: date to process

Return : Returns a value of type BigInteger

date.add

Objective : Adds to a date an Integer number with a specific type of increment

  • 3 Arguments:

Number of increments: Integer number Type of addition: Choice of the type of increment contained in the following list:

  • Semesters

  • Quarters

  • Month

  • Weeks

  • Days

  • Hours

  • Minutes

  • seconds

  • Milliseconds

Date value: Specifies the date on which the operation is performed

Return : Returns a value of Integer type

date.add(3, month, 01/12/2017) RETURNS 04/12/2017
date.add(3, month, MARTIN) RETURNS Null
date.add(TEST, month, 01/12/2017) RETURNS Null

date.to_str

Objective : Transforms a date type value into character string

  • 2 Arguments:

Date to transform: Date type value to process Masque: Mask of the date once transformed into a String to choose from a list

Return : Returns a value of character string type

date.to_str(2017-01-12 00:00:00, 'Weekday name + year') RETURNS Thursday - 2017

date.diff

Objective : Realizes the difference between two dates (First date - Second date) by specifying a calculation unit

  • 3 Arguments:

Calculation unit: Type of unit used to calculate the date difference First date: Value of the first date (Date) Second date: Value of the second date (Date)

Return : Returns a numeric value

date.diff("Days", 01/22/2010,2010-01-10) RETURNS 12

date.is_date

Objective : Returns a boolean if the value is a date

  • 2 Arguments:

Value to process: Value to analyze Reading mask: Specifies the reading mode of the value to be analyzed

Return : Returns a boolean value (true or false)

date.is_date(01/12/1956,’dd/mm/yyyy’) RETURNS True
date.is_date(98/01/1956,’dd/mm/yyyy’) RETURNS False
date.is_date(01/12/1956,’dd-mm-yyyy’) RETURNS False
date.is_date(MARTIN,’dd-mm-yyyy’) RETURNS False

Please note that the formula only works if the value to be analyzed is contained in a column of type date.

date.transform

Objective : Transforms the format of a date into an other date format

  • 3 Arguments:

Value: Date to transform Reading format: mask for reading the value to be processed Output format: output mask of the value to be processed

Return : Returns a value of character string type

date.transform(2012-01-12, yyyy-mm-dd, month - year ) RETURNS January - 2017

date.from_timestamp

Objective : Gives the Date corresponding to a Posix time (number of seconds elapsed since January 1, 1970 00:00:00 UTC until the event to be dated)

  • 1 Argument:

Value: BigInteger value to process

Return : Returns a Date type value

date.from_timestamp(1544519129738) RETURNS 01/12/2023 12:22:21

date.deducted

Objective: Deduce a date from another date by specifying a pre-defined function

2 arguments:

Value 1: Valid Date Value 2: Pre-defined function

possible type
    Start of month
    End of month
    Start Week
    Weekend
    Start of term
    End of Quarter
    Start of semester
    End of semester
    Beginning of Year
    End of year

Return Returns a value of type Date

date.deducted(12/02/2020, End of Month). REFERRED 02/29/2020

date.generate

Objective: Generation of a list of DateTime type according to an increment and an increment type

3 arguments:

Value 1: Type_Increment in a closed list (year, month, days, minutes, second) Value 2: Value of the Numeric Integer type increment. Value 3: Value: Date / Time Valid.

Return: Returns a value of type list of Date

date.generate(Days,4, 01/01/2020) RETURNS [01/01/2020, 01/02/2020, 03/01/2020, 04/01/2020]

date.list.as_str

Objective : Transforms a list of dates into a character string separated by the comma character,

1 argument:

value 1: Date list (List type)

return: Returns a character string type

date.list.max_occur

Objective : Returns the most frequent date (or dates) in a list of dates

1 argument:

value 1: Date list (List type)

return: Returns a Date List type

date.list.min_occur

Objective : Returns the least frequent date (or dates) in a list of dates

1 argument

value 1: Date list (List type)

return: Returns a Date List type

date.list.contains

Objective : Tests the presence in a list of a character string

2 arguments:

Value 1: Character string list (Type List) Value 2: Character string value to search for

Return: Returns a boolean True or False

date.list.contains(["Pierre", "André", "Paul"], "Pierre") RETURNS True
date.list.contains(["Pierre", "André", "Paul"], "Marcel") RETURNS False

date.list.create

Objective: Realize the creation of a date list from a sequence of dates

1 argument:

Value 1: Date sequence

Return: Returns a list of date type

date.list.create([12/01/2019, 13/02/2020, 15/01/2020] RETURNS ["12/01/2019", "13/02/2020", "15/ 01/2020"]

date.list.max

Objective : Returns the largest date in a list of dates

1 argument:

Value 1: List of date type dates

Return: Returns a type date value

date.list.max([01/12/2020, 01/19/2020, 01/15/2020]) RETURNS 01/19/2020

date.list.min

Objective : Returns the smallest date in a list of dates

1 argument:

Value 1: List of date type dates

Return: Returns a type date value

date.list.min([01/12/2020, 01/19/2020, 01/15/2020]) RETURNS 01/12/2020

date.list.sort

Objective : Performs the ascending sort of a list of dates

1 argument:

Value 1: List of Date type dates

Return: Returns a Date type list

date.list.sort([01/12/2020, 01/19/2020, 01/15/2020]) RETURNS [01/12/2020, 01/15/2020, 01/19/2020]

date.list.get

Objective : Returns a date value included in a list according to its index in the list

2 arguments:

Value 1: Date list (List type) Value 2: Index (Integer type)

Return: Returns Date type value

date.list.get([2020-01-12, 2020-01-19, 2020-01-15], 2) RETURNS 2020-01-15

date.list.intersect

Objective : Returns a list of Date values ​​in common between two lists of type Date

2 arguments:

Value 1: Date Lists (List type) Value 2: Date Lists (List type)

Return: Returns a list of Date

date.list.union

Objective : Returns a list containing all the Date values ​​of the two date-type lists

2 arguments:

Value 1: Date Lists (List type) Value 2: Date Lists (List type)

Return: Returns a list of Date

date.list.diff

Objective : Returns a list containing all the different Date values ​​between two Date type lists

2 arguments:

Value 1: Date Lists (List type) Value 2: Date Lists (List type)

Return: Returns a list of Date

Date constants

date. tomorow

Objective: Returns today’s date

date.today

Purpose: Returns yesterday’s date

date. yesterday

Objective: Returns tomorrow’s date

date.week_start

Objective: Returns the week start date of the current day

date.weekend

Purpose: Returns the start date of the weekend of the current day

date.month_start

Objective: Returns the start date of the month of the current day

date.month_end

Objective: Returns the end date of the month of the current day

date.quarter_start

Objective: Returns the start date of the current day’s quarter

date.quarter_end

Objective: Returns the end date of the quarter of the current day

date.year_start

Objective: Returns the start date of the year of the current day

date.year_end

Objective: Returns the start date of the year of the current day

Unit formulas for the Boolean type

bool.not

Objective : Inverts the result of a boolean

  • 1 Argument: Boolean type value: Boolean to invert

Return : Returns a value of Boolean type

bool.not(True) RETURNS False
bool.not(False) RETURNS True
bool.not(MARTIN) RETURNS Null

bool.replace_bool_null

Objective : Replaces the Null value of a Boolean type cell with a specified value

  • 2 Arguments:

Value 1: Character string to process Value 2: Default value if value 1 is null

Return : Returns a value of type String

bool.replace_bool_null(Null, False) RETURNS False
bool.replace_bool_null(True, -1) RETURNS True

bool. false

Objective: Returns a constant value = False

bool.true

Objective: Returns a constant value = True

bool.is_false

Objective: Returns False

bool.is_true

Objective: Returns True

bool.to_str

  *Objective* :
*Transforms* a *Boolean* type value into a *String* type value

  • 1 Argument:

Boolean value to process

Return : Returns a value of type String

bool.to_int

  *Objective* :
*Transforms* a *Boolean* type value into an *Integer* type value

  • 1 Argument:

Boolean value to process

Return : Returns a value of Integer type

Unit formulas for Geometry functions

geo.distance

Objective : Calculates the direct distance (as the crow flies) between two geocoded points

  • 5 Arguments: Starting latitude: Latitude of the starting point Longitude of departure: Longitude of the starting point Arrival latitude: Latitude of the arrival point Longitude of arrival: Longitude of the point of arrival Unit: Kilometer, Maritime Knot, Mile

Note that if point 1 is less than point 2 the result will be Negative

Return : Returns a value of decimal type

geo.distance(45.3456, 2.3456, 46.345,3.456, kilometer) RETURNS 140.514478462

geo.google.geocode

Objective : Geocode an address

  • 1 Argument: Value to process: Represents a valid postal address

Return : Returns a value of type Json String

geo.google.geocode(55 Rue du Faubourg Saint-Honoré, 75008 Paris). RETURNS { "lat": 48.87060109999999, "lng": 2.3169533}

geo.contains

Objective : Indicates if a shape in Json format is contained in an other shape in Json format

  • 2 Arguments: Json 1: String in Json format containing a description of Shape Json 2: String in Json format containing a description of Shape

Return: Returns a value of Boolean type

Example :

Json 1
 {"type":"Polygon","coordinates":[[[0.87890625,46.195042108660154],[2.724609375,46.195042108660154],[2.724609375,47.18971246448421],[0.87890625,47.18971246448421],[0.87890625,46.195042108660154]]]}
Json 2
{"type":"Polygon","coordinates":[[[1.47491455078125,46.51540570001737],[2.2247314453125,46.51540570001737],[2.2247314453125,46.91837932415156],[1.47491455078125,46.91837932415156],[1.47491455078125,46.51540570001737]]]}
geo.contains(Json 1, Json 2) RETURNS False

geo.contains_point

Objective : Returns True if points defined in Latitude-Longitude are contained in a JSON shape

  • 3 Arguments: Json 1: String in Json format containing a description of Shape Latitude: Latitude Longitude: Longitude

Return : Returns a value of Boolean type

True if the point is included in the Shape in valid Json format False if the point is not included in the Shape in valid Json format

json 1
{"type":"Polygon","coordinates":[[[1.47491455078125,46.51540570001737],[2.2247314453125,46.51540570001737],[2.2247314453125,46.91837932415156],[1.47491455078125,46.91837932415156],[1.47491455078125,46.51540570001737]]]}
Latitude: 46.80334963091315
Longitude: 1.6925811767578125
geo.contains_point(Json 1, Latitude, Longitude) RETURNS True

geo.intersect

Objective : Indicates if a shape in Json format is in intersection with an other shape in Json format

  • 2 Arguments: Json 1: String in Json format containing a description of Shape Json 2: String in Json format containing a description of Shape

Return : Returns a value of Boolean type

Json Example 1
{"type":"Polygon","coordinates":[[[-4.932861328124999,41.66470503009207],[-3.328857421875,41.66470503009207],[-3.328857421875,42.52069952914966],[-4.932861328124999,42.52069952914966],[-4.932861328124999,41.66470503009207]] ]}
Json Example 2
{"type":"Polygon","coordinates":[[[-5.2459716796875,41.549700145132725],[-4.7515869140625,41.549700145132725],[-4.7515869140625,41.92271616673922],[-5.2459716796875,41.92271616673922],[-5.2459716796875,41.549700145132725]] ]}
geo.intersect(Json 1, Json 2) RETURNS True

geo.google.way_value

Objective : Calculates the route between two points with a specific mode of transport

  • 5 Arguments: Longitude 1: Longitude of Point 1 Latitude 1: Latitude of Point 1 Longitude 1: Longitude of Point 2 Latitude 2: Latitude of Point 2 Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Integer (meters)

geo.google.way_value(45.02452388545682, 5.6256866455078125, 45.15686396890044, 5.6256866455078125, Driving) RETURNS 2300

geo.google.traject_distance_value

Objective : Calculates the route between two points with a specific mode of transport. Uses the Google route calculation API.

  • 5 Arguments: Longitude 1: Longitude of Point 1 Latitude 1: Latitude of Point 1 Longitude 1: Longitude of Point 2 Latitude 2: Latitude of Point 2 Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Integer (meters)

geo.google.traject_distance_value(45.02452388545682, 5.6256866455078125, 46.02452388545682, 5.8256866455078125, By car) RETURNS 147881

geo.google.traject_distance_object

Objective : Calculates the route between two valid addresses with a specific mode of transport. Uses Google API for route calculation

  • 3 Arguments: Address 1: Valid address in character string Address 2: Address 2 Valid in character string Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Integer (meters)

geo.google.traject_distance_object(56 rue du four 38330 Saint Ismier, 305 rue Aristide bergès 38330 Monbonnot-saint-martin, By car) RETURNS 4664

geo.google.way_object

Objective : Returns the full response from the Google API in Json form

  • 3 Arguments: Address 1: Valid address in character string Address 2: Address 2 Valid in character stringeras Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Json-character-string

geo.google.way_object(56 rue du four 38330 Saint Ismier, 305 rue Aristide bergès 38330 Montbbonnot Saint Martin,By car)

RETURNS

{ "request": { "origin": { "location_adr": "45 rue du Four 38330 Saint ismier" }, "destination": { "location_adr": "305 rue Aristide bergès 38330 Montbonnot Saint Martin" }, "travel_mode" : "DRIVING" }, "result": { "geocoded_waypoints": [ { "geocoder_status": "OK", "partial_match": true, "place_id": "ChIJ3VDekZNYikcRgGu-5CqrCAQ", "types": [ "LOCALITY", "POLITICAL" ] }, { "geocoder_status":
"OK", "partial_match": false, "place_id": "ChIJj-tVJExfikcRnf-9P45Sf4c", "types": [ "PREMISE" ] } ], "routes": [ { "summary": "D11B", "legs " : [ { "steps": [ { "html_instructions": "Take direction <b>north-west</b> on <b>Montée des Tilleuls</b> towards <b>Chemin du Rozat</b>", "distance": { "in_meters": 37, "human_readable": " 37m" }, "duration": { "in_seconds": 9, "human_readable": "1 minute" }, "start_location": { "lat": 45.2484512, "lng": 5.8276394 }, "end_location": { "lat": 45.2487269, "lng": 5.8273833 }, "polyline": { "encoded_path": " yqdsGweqb@MDQLEBEFKR" }, "travel_mode": "DRIVING" }, { "html_instructions": "Take <b>left</b> on <b>Chemin du Rozat</b>", "distance": { "in_meters": 90, " human_readable": "90 m" }, "maneuver": "turn-left", "duration": { "in_seconds": 43, "human_readable": "1 minute" }, "start_location": { "lat": 45.2487269, "lng": 5.8273833 }, "end_location": { "lat": 45.2483116, "lng": 5.8264078 }, "polyline": {
"encoded_path": "qsdsGcdqb@FHFHBH~@bD" }, "travel_mode": "DRIVING" }, { "html_instructions": "Continue on <b>Charming Som Path</b>", "distance": { " in_meters": 231, "human_readable": "0.2 km" }, "duration": { "in_seconds": 50, "human_readable": "1 minute" }, "start_location": { "lat": 45.2483116, "lng": 5.8264078 } , "end_location": { "lat": 45.2471763, "lng":
5.8239934 }, "polyline": { "encoded_path": "}pdsGa~pb@@V@J@PBV@LFLZn@JNdAdCZp@d@bA\\|@" }, "travel_mode": "DRIVING" }, { " html_instructions": "Take <b>left</b> on <b>Path by Ray Buisson</b>", "distance": { "in_meters": 423, "human_readable": "0.4 km" }, "maneuver": "turn-left", "duration": { "in_seconds" :60, "human_readable": "1 minute" }, "start_location": { "lat":
45.2471763, "lng": 5.8239934 }, "end_location": { "lat": 45.2443276, "lng": 5.8274246 }, "polyline": { "encoded_path":
"{idsG}npb@j@i@v@u@XYFMZu@j@iB@CHYJOPWfAeBBGVUn@e@XWn@c@XYPQh@a@LK" }, "travel_mode": "DRIVING" }, { "html_instructions": "<b>Chemin de Ray Buisson</b> turns slightly <b>to the right</b> and becomes <b>Route du Rivet</b>/<b>D11B</b><div style=\"font-size:0.9em\">Continue following D11B< /div>", "distance": { "in_meters": 2837, "human_readable": "2.8 km" }, "duration": { "in_seconds": 199, "human_readable": "3 minutes" }, "start_location": { "lat": 45.2443276, "lng": 5.8274246 }, "end_location": { "lat": 45.22451419999999, "lng": 5.8199369 }, "polyline":
{ "encoded_path":
"axcsGkdqb@B@B?BA\\UPG`@QLG`@[ZYHOdAqAb@q@dAeB^a@^a@T_@DKv@sA\\g@^a@x@aAJI^]Z_@l@s @l@s@j@o@l@k@\\U\\Q\\M^KTCFA\\C`@?b@?\\B\\F^JPHLFn@b@^\\\\^ NTHLZf@Xn@Vl@d @fAl@~Ab@fA`@~@DHTj@`@bAjAtC`@bA`@dATp@FPr@dBz@pBh@nAd@dAh@dAr@pAf@v@HLRZt@|@d@f@b@` @?@`@VVPVJ^LZJ`@Hp@Fr@@r@GXEl@Mj@U`@Sd@YXWVS`As@z@a@r@Sv@Gh@Hj@Th @X`@d@\\b@\\p@lArCBHdAhCl@lAx@nAd@d@DPJJXVf@Zd@Pp@P`@Hf@H`@HJFJL" }, "travel_mode": "DRIVING" }, { "html_instructions": "At the roundabout, take the <b>1st</b> exit onto <b>Route de la Doux</b>/<b>D11</b>", "distance": { "in_meters": 92, "human_readable": "92 m" }, "maneuver": "roundabout-right", "duration ": { "in_seconds": 12, "human_readable": "1 minute" }, "start_location": { "lat": 45.22451419999999, "lng": 5.8199369 }, "end_location": { "lat": 45.2239935, "lng": 5.8190593 }, "polyline": { "encoded_path":
"e|_sGsuob@@F@DBDBBBBDBBBD@LLHLJTNb@Rr@" }, "travel_mode": "DRIVING" }, { "html_instructions": "Turn <b>left</b> at <b>Chemin du Moulin< /b>", "distance": { "in_meters": 273, "human_readable": "0.3 km" }, "maneuver": "turn-left", "duration": { "in_seconds": 95, "human_readable": "2 minutes" }, "start_location": { "lat ": 45.2239935, "lng": 5.8190593 }, "end_location": {
"lat": 45.222679, "lng": 5.819291499999999 }, "polyline": { "encoded_path":
"}x_sGcpob@JGZIRKJOJQBGHWDKDKBEFKDMDMBKDMBM@M@K@IBI@IBK@IFEF?PFDFT`ABJ@NFXBLBP@N@N?T@FDL" }, "travel_mode": "DRIVING" }, { "html_instructions": "Take <b >to right</b> on <b>Rue Aristide Berges</b>", "distance": { "in_meters": 125, "human_readable": "0.1 km" }, "maneuver": "turn-right" , "duration": { "in_seconds": 28, "human_readable": "1 minute" }, "start_location": { "lat": 45.222679, "lng": 5.819291499999999 }, "end_location": { "lat": 45.2225971, "lng": 5.8180214 }, "polyline": { "encoded_path":
"wp_sGqqob@OZQ\\ELCL?LBHFJHHRJFFDFDN?VAt@" }, "travel_mode": "DRIVING" }, { "html_instructions": "Turn <b>left</b> to stay on <b>Rue Aristide Berges< /b><div style=\"fontsize:0.9em\">Your destination will be on the left.</div>", "distance": { "in_meters": 18, "human_readable": "18 m" }, "maneuver": "turn-left", "duration": { " in_seconds": 3, "human_readable": "1 minute" }, "start_location": { "lat": 45.2225971, "lng": 5.8180214 }, "end_location": { "lat": 45.2224677, "lng": 5.817895 } , "polyline": { "encoded_path": "gp_sGsiob@LJHH@@" }, "travel_mode":
"DRIVING" } ], "distance": { "in_meters": 4126, "human_readable": "4.1 km" }, "duration": { "in_seconds": 499, "human_readable": "8 minutes" }, "start_location": { "lat": 45.2484512, "lng":
5.8276394 }, "end_location": { "lat": 45.2224677, "lng": 5.817895 }, "start_address": "38330 Saint-Ismier, France", "end_address": "305 Rue Aristide Berges, 38330 Montbonnot-Saint-Martin , France" }], "waypoint_order": [ ], "overview_polyline": { "encoded_path": "yqdsGweqb@_@RKJKRFHJR~@bD@VB\\Dd@b@|@rCjG\\|@j@i@ pAoAb@cAl@mBHYJOxA}BZ]hA}@n@c@XYz@s@LKB@FAn@]n@Y|@u@nAaBhBwC~@cApBgDxAcBj@g@bDwDl@k@\\Uz@_@t@ Od@Ed A?z@Jp@T|@j@|@|@Xb@t@vA|@tBxBpFdDhIrBnFdB`EnAjCzAhC\\h@zAdBb@b@x@h@v@X|@Tp@Fr@@lAMl@Mj @UfAm@p@k@`As@z@a@r@Sv@Gh@Hj@Th@X`@d@z@tApA|CrBvEx@nAd@d@P\\`Ar@vAb @hAR`@HJFLTLR\\VTb@b@vAf@QRKJONYNc@Vk@Ru@Hm@DUHOXFZhAPbAFfAFTa@x@IZBVPTZRJVAlAXV" }, "bounds": { "northeast": { "lat": 45.2487269, "lng": 5.8326233 }, "southwest": { "lat": 45.2224677, "lng": 5.817895 } }, "copyrights": "Map data ©2018 Google", "warnings": [ ] } ] }}

geo.google.traject_duration_value

Objective : Calculates the travel time between two points with a specific mode of transport. Uses the Google API for travel time calculation.

  • 5 Arguments: Longitude 1: Longitude of Point 1 Latitude 1: Latitude of Point 1 Longitude 1: Longitude of Point 2 Latitude 2: Latitude of Point 2 Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Integer (meters)

geo.google.traject_duration_value(45.02452388545682, 5.6256866455078125, 45.15686396890044, 5.6256866455078125,Drive) RETURNS 3501

geo.google.traject_duration_object

Objective : Calculates the travel time between two valid addresses with a specific mode of transport. Uses the Google API for travel time calculation.

  • 3 Arguments: Address 1: Valid address in character string Address 2: Address 2 Valid in character string Mode of transport: Choice of the mode of transport for calculating the route. By car, on foot, by bike, public transport.

Return : Returns a value of type Integer (meters)

geo.google.traject_duration_object(45 allée du Four 38330 Saint ismier, 305 Rue Aristide Berges 38330 Montbonnot Saint Martin,By car) RETURNS 588

geo.st_area

Objective : Returns the area of ​​a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Return: Decimal

geo.st_as_geojson

Objective : Returns the GeoJSON of a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Return: Character string in GeoJson format

geo.st_as_text

Objective : Returns the WKT of a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Return: Character string in WKT format

geo.st_buffer

Objective: Returns a geometry/geography that represents all points whose distance from this geometry/geography is less than or equal to a given distance.

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Return: Geometry

geo.st_centroid:

Objective: Returns the centroid point of a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Back: Geometry

geo.st_contains

Objective: Returns true if geometry 1 entirely contains geometry 2

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: Second geometry to compare

Return: boolean

geo.st_convex_hull

Objective : Returns the convex hull of a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Back: Geometry

geo.st_crosses

Objective : Returns true if geometry 1 intersects geometry 2

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: Second geometry to compare

Return: boolean

geo.st_distance

Objective : Calculates the Euclidean distance between two points

  • 2 Arguments

Geometry 1: First geometry Geometry 2: Second geometry

Return: Decimal

geo.st_envelope

Objective: Returns the envelope of a geometry

  • 1 Argument

Geometry: Geometry to analyze (Geometry type)

Back: Geometry

geo.st_equals:

Objective: Returns true if geometry 1 is equal to geometry 2"

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: Second geometry to compare

Return: boolean

geo.st_geom_from_geojson

Objective : Constructs a geometry from a GeoJson

  • 1 Argument

GeoJson: Character string in GeoJson format (Type String)

Back: Geometry

geo.st_geom_from_wkb

Objective : Constructs a geometry from a WKB format

  • 1 Argument

WKB: Character string in WKB format (Type String)

Back: Geometry

geo.st_geom_from_wkt

Objective : Constructs a geometry from a WKT format

  • 1 Argument

WKT: Character string in WKT format (Type String)

Back: Geometry

geo.st_geometry_type

Purpose: Returns the type of a geometry

  • 1 Argument

_Geometry: Geometry to analyze (Geometry Type)

Return: Character string

geo.st_intersection

Objective : Returns the intersection between two geometries

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: Second geometry to compare

Back: Geometry

geo.st_intersects

Objective : Checks if two geometries intersect

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: Second geometry to compare

Return: boolean

geo.st_is_simple

Objective : Tests if the geometry’s only self-intersections are at boundary points

  • 1 Argument

Geometry: Geometry to analyze

Return: boolean

geo.st_is_valid

Objective : Tests if a geometry is valid

  • 1 Argument

Geometry: Geometry to analyze

Return: boolean

geo.st_length

Objective : Returns the perimeter of a geometry

  • 1 Argument

Geometry: Geometry to analyze

Return: Decimal

geo.st_line_string_from_text

Objective : Constructs a geometric line from a string

  • 2 Arguments

Argument 1: Character string to analyze Separator: Specify the separator

Back: Geometry

geo.st_make_valid

Objective : Performs the creation of a valid representation of an invalid geometry (Polygon or Multipolygon)

  • 2 Arguments

Geometry: Geometry to analyze Separator: Specify if holes should be removed (Boolean)

Back: Geometry

geo.st_n_points

Objective : Returns the points of a geometry

  • 1 Argument

Geometry: Geometry to analyze

Return: Integer

geo.st_overlaps:

Objective : Returns true if geometry 1 overlaps geometry 2

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: First geometry to compare

Return: boolean

geo.st_point

Objective : Constructs a geometric point

  • 2 Arguments

First value: Value of the first point (Latitude) Second value: Value of the first point (Longitude)

Back: Geometry

geo.st_point_from_text

Objective : Constructs a geometric point from a character string

  • 2 Arguments

Character string: Specifies the character string to process Separator: Specifies the separator character*Back*: Geometry

geo.st_polygon_from_envelope

Objective :

Constructs a geometric polygon from an envelope

  • 4 Arguments

Argument 1: Specifies the Minimum X Argument 2: Specifies the Minimum Y Argument 3: Specifies the Maximum X Argument 4: Specifies the Maximum Y

Back: Geometry

geo.st_polygon_from_text

Objective: Build a Geometric Polygon from a character string

  • 2 Arguments

Character string: Specifies the character string to process Separator: Specifies the separator character

Back: Geometry

geo.st_precision_reduce

Objective : Reduces the precision of Decimals in geometry coordinates. The last decimal will be rounded

  • 2 Arguments

Geometry: Specifies the geometry to be processed Separator: Number of decimals

Back: Geometry

geo.st_simplify_preserve_topology: Ge

Objective : Simplifies a geometry

  • 2 Arguments

Geometry: Specifies the geometry to be processed Separator: Distance tolerance

Back: Geometry

geo.st_touches

Objective : Returns true if geometry 1 touches geometry 2

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: First geometry to compare

Return: boolean

geo.st_transform

Objective : Transforms the spatial reference system / coordinate reference system of a geometry

  • 2 Arguments

Geometry 1: Geometry to be analyzed SRC: Source SRC SRC: Destination SRC

Back: Geometry

geo.st_within

Objective : Returns true if geometry 1 is entirely contained by geometry 2

  • 2 Arguments

Geometry 1: First geometry to compare Geometry 2: First geometry to compare

Return: boolean

Global list functions

list.size

Objective : Returns the size of a list.

  • 1 Argument: Value 1: List to process

Return : Returns a value of Integer type

list.size(["45","12","12","14","23"]) RETURNS 5

Functions on URLs

url.extract_params

Objective : Extracts parameters from a URL.

  • 1 Argument: Value: Give here a Valid URL to process

Return : Returns a value of type Object string

url.extract_params(https://www.google.com/search?q=wikipedia+GRENOBLE&rlz=1C5CHFA_enFR864FR864&oq=wikipedia+GRENOBLE&aqs=chrome..69i57j0l7.4545j0j7&sourceid=chrome&ie=UTF-8) RETURNS {"sourceid" :"chrome","q":"wikipedia+GRENOBLE","rlz":"1C5CHFA_enFR864FR864","oq":"wikipedia+GRENOBLE","aqs":"chrome..69i57j0l7.4545j0j7","ie": "UTF-8"}

url.extract_uri

Objective :

Performs URI extraction from a URL.

  • 1 Argument: Value: Give here a Valid URL to process

Return : Returns a value of character string type

url.extract_uri(https://www.google.com/search?q=wikipedia+GRENOBLE&rlz=1C5CHFA_enFR864FR864&oq=wikipedia+GRENOBLE&aqs=chrome..69i57j0l7.4545j0j7&sourceid=chrome&ie=UTF-8) RETURNS search

url.encode_url

Objective : Perform url encoding

  • 1 Argument: Value 1: Url to encode

Return : Returns a value of character string type

url.encode_url(https://fr.wikipedia.org/wiki/24 Hours of Le Mans) RETURNS https://fr.wikipedia.org/wiki/24%20Hours%20of%20Mans

url.encode_string

Objective : Realizes an encoding of the parameters of a URL

  • 1 Argument: Value 1: Character string to encode

Return : Returns a value of character string type

url.encode_string(24 Hours of Le Mans) FORWARDING 24+Heures+du+Mans

DataChain Specific Functions

dc.ranking

Cf Documentation Classification

Objective : Applies a DataChain collation to a character string. (Consumes a generated collation in the ranking function of DataChain’s HandleData)

  • 2 Arguments:

Value to process: Character string to classify (the type of value depends on the type of classification) DataChain classification to apply: Specifies the DataChain classification that is consumed

A classification of type Integer classifies Integers A classification of type Decimal classifies Decimals A classification of type Date classifies Dates A character string classification classifies character strings

Return : Returns Null if the class type does not match the type of the valuetreat

dc.ranking(Age,Age Ranking)

dc.to_str

Objective : Convert a value to a string

  • 1 Argument: Value 1: Character string to process

Return : Returns a value of character string type

dc.to_str(123.23) RETURNS 123.23

dc.extract_json

Objective : Returns a character string from an expression in valid JSON format via the definition of a path (JSON Path)

  • 2 Arguments: Value to process:Expression in valid Json format to process Json Path: JsonPath path leading to the value to extract

Return : Returns a value of character string or JsonCharacterString type

dc.extract_json({ "lat" : 48.87060109999999, "lng" : 2.3169533},$.lat) RETURNS 48.87060109999999

word.to_str

Objective : Performs the transformation of a Words type object into Json containing a breakdown per word associated with information characterizing each word

  • 1 Argument: Value 1: Word Object

Return : Returns a value of JsonCharacterString type

word.to_str(str.to_words(It’s sunny today. That’s fine.) RETURNS [{"term":"He","term_length":2,"term_number_in_text":1,"term_number_in_paragraph ":1,"term_number_in_sentence":1,"sentence_number_in_text":1,"sentence_number_in_paragraph":1,"paragraph_number_in_text":1},{"term":"fact","term_length":4,"term_number_in_text":2, "term_number_in_paragraph":2,"term_number_in_sentence":2,"sentence_number_in_text":1,"sentence_number_in_paragraph":1,"paragraph_number_in_text":1},{"term":"beautiful","term_length":4,"term_number_in_text": 3,"term_number_in_paragraph":3,"term_number_in_sentence":3,"sentence_number_in_text":1,"sentence_number_in_paragraph":1,"paragraph_number_in_text":1},{"term":"ce","term_length":2,"term_number_in_text ":4,"term_number_in_paragraph":4,"term_number_in_sentence":4,"sentence_number_in_text":1,"sentence_number_in_paragraph":1,"paragraph_number_in_text":1},{"term":"day.","term_length":5 ,"term_number_in_text":5,"term_number_in_paragraph":5,"term_number_in_sentence" :5,"sentence_number_in_text":1,"sentence_number_in_paragraph":1,"paragraph_number_in_text":1},{"term":"C'","term_length":2,"term_number_in_text":6,"term_number_in_paragraph":6, "term_number_in_sentence":1,"sentence_number_in_text":2,"sentence_number_in_paragraph":2,"paragraph_number_in_text":1},{"term":"est","term_length":3,"term_number_in_text":7,"term_number_in_paragraph": 7,"term_number_in_sentence":2,"sentence_number_in_text":2,"sentence_number_in_paragraph":2,"paragraph_number_in_text":1},{"term":"very","term_length":4,"term_number_in_text":8,"term_number_in_paragraph ":8,"term_number_in_sentence":3,"sentence_number_in_text":2,"sentence_number_in_paragraph":2,"paragraph_number_in_text":1},{"term":"good.","term_length":5,"term_number_in_text":9 ,"term_number_in_paragraph":9,"term_number_in_sentence":4,"sentence_number_in_text":2,"sentence_number_in_paragraph":2,"paragraph_number_in_text":1}]

dc.if

Objective : Checks if the condition is met and returns one value if the result of a condition you specified is TRUE, and another value if the result is FALSE.

  • 5 Arguments: Value 1: Character string to process Operator: Choice of operator applied for the test Test Value 1: First Test Value (Not mandatory in the case of IsNull or IsEmpty type operators) Value if True: Returns this character string if the rule is True Value if False: Returns this character string if the rule is False

Return : Returns a value of character string type

dc.if(AUDI A3 color Black, contains, Black, Dark, Light) RETURNS Dark

dc.compare

Objective : Allows to compare values ​​with each other using an operator

  • 3 Arguments:

Value 1: First value to compare Operator: Operator used for comparison Value 2: Second value to compare Value 3_: Third value to compare (this value is only used for operators requiring this value as included in the interval, outside the interval, …)

Return : Returns a value of Boolean type

dc.compare(12, in the range,10,15) RETURNS True
dc.compare(17, in the range,10,15) RETURNS False

dc.rule

Objective: This formula makes it possible to generate complex rules with parameters thanks to the QueryBuilder of the solution DataChain

Return : Returns a value of character string type

Presentation dc-rule

1 Formula dc.rule expects 3 arguments 2 Argument 1: Rule built with DataChain QueryBuider 3 Argument 2: Value returned by the formula if the rule returns True 4 Argument 3: Value returned by the formula if the rule returns False 5 Block containing information about the current formula

Creation steps:

Steps dc-rule

1 Click to display the list of possible arguments 2 Choose Rule to open the rules popup 3 Choose a column to compare (here Species), an operator (here Equals) and another item to compare (here it’s another column * Name selected*) Note: to search in a column, use the icon List 4 Click on validate to close the popup 5 Specify the character string to return if the rule returns TRUE 6 Specify the character string to return if the rule returns FALSE

dc.advanced_position

Objective: Gives the position of a value in a character string according to a reading direction (Start or End), and by specifying the Nth index of the character sought.

  • 4 Arguments:

Argument 1: Character string to process Argument 2: Character to search for Argument 3: reading direction of the character string - Start or End Argument 4: Nth Index of the sought character. indexes start at 0

dc.advanced_position('search for a character in a string',''',Start,0) RETURNS 11

In the character string, the @ character with index 0 (i.e. the first) is in position 11 from the start

dc.advanced_position('search for a character in a string',''',Start,1) RETURNS 26

In the character string, the @ character with index 1 (i.e. the second) is in position 26 from the start

file.extension

Objective : Returns the extension of a filename

  • 1 Argument:

Value 1: Character string to process

Return : Returns a value of type String

file.extension(Myfile.pdf) RETURNS pdf

http.post

Objective : Allows you to make a service call with the POST method via HTTP protocol by passing an optional URL and Body.

  • 2 Arguments: Value 1: Character string in URL format to process Value 2: Character string (Option)

Return: The content of the return is specific to the service performed

http.get

Objective : Allows you to make a service call with the GET method via HTTP protocol

  • 1 Argument: Value 1: URL to execute

Return : The return depends on the service performed

http.get(http://geodesy.geo.admin.ch/reframe/lv95towgs84?easting=2600000&northing=1200000&altitude=550.0&format=json)

bin.content_object

Objective : Turn a binary into an object

  • 1 Argument: Value: Binary

Return : Returns a value of type Object

bin.content_str

Objective : Returns the contents of a binary

  • 1 Argument: Value: character string

Return : Returns a value of character string type

bin.content_words

dc.free_list

Objective : Allows you to enter a formula using valid SparkSql functions. Returns a List type value

  • 1 Argument: Value: Character string in valid SparkSql format. Use of column codes

Return : Returns a value of List type

dc.free

Objective : Allows you to enter a formula using valid SparkSql functions. Returns a value of type string

  • 1 Argument: Value: Character string in valid SparkSql format. Use of column codes

Return : Returns a value of character string type

dc.null

Objective: Returns Null