Fonctions DataChain
genericsData & 
HandleData
ta, 20] genericsData & HandleData
The formulas Datachain enable data to be processed and cross-referenced at different strategic levels of the value chain: Business Entities, DataBlocks and HandleData data sources.
These formulas call functions, characterised 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.
|
Some formulas are marked as deprecated (displayed crossed out in DataChain): they will be removed in the next major version (9.0). We recommend that you stop using these formulas in favour of other formulas specified in the title of each deprecated formula. |
Unitary formulas for Decimal type numerics
dec.list.position
Purpose: To know the position of a value in a list. The position is not based on zero, but on an index based on 1.
-
2 Arguments: List : List of decimal values Value: Decimal values to search for in the list
Returns: Returns the position in the list of the value of argument 2. + Return Returns -1 if argument 2 is absent.
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
Unit formulas for numerics of Integer type
int.list.position
Purpose: To know the position of a value in a list. The position is not based on zero, but on an index based on 1.
-
2 Arguments: List: List of intergers values Value: Interger value to look for in the list
Returns: Returns the position in the list of the value of argument 2. + Return Returns -1 if argument 2 is absent.
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
Unitary formulas for numerics of type BigInteger
bigint.list.position
Purpose: To know the position of a value in a list. The position is not based on zero, but on an index based on 1.
-
2 Arguments: List: List of biginterger values Value: Biginterger value to search for in the list
Returns:
Returns the position in the list of the value of argument 2.
Returns -1 if argument 2 is not present.
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
Unitary formulas for the Character string type
str.list.position
Purpose: To know the position of a value in a list. The position is not based on zero, but on an index based on 1.
-
2 Arguments: List: List of string values Value: Value to search for in the list
Returns: Returns the position in the list of the value of argument 2. + Return Returns -1 if argument 2 is absent.
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.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
DEPRECIATED: prefer the use of str.to_datez (below)
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_datez
Purpose: Transforms a string into a date using the provided timezone and language.
- 4 Arguments
-
date : string to transform masque: date mask to apply as input timezone: time zone to use language: language to be used if necessary
Returns : Returns the date in the time zone and language defined in Date format.
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
str.to_
Purpose: Transforms a string into a date using the provided timezone and language.
- 4 Arguments
-
date : string to transform masque: date mask to apply as input timezone: time zone to use language: language to be used if necessary
Returns : Returns the date in the time zone and language defined in Date format.
Unit formulas for the Date type
date.list.position
Purpose: To know the position of a value in a list. The position is not based on zero, but on an index based on 1.
-
2 Arguments: List : List of values of type Date Value: Value to search for in the list
Returns: Returns the position in the list of the value of argument 2. +: Returns the position in the list of the value of argument 2. Returns -1 if argument 2 is absent.
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
DEPRECIATED: prefer the use of date.to_strz (below)
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.to_strz
Purpose: Transforms a date into a string using the time zone and language provided.
- 4 Arguments
-
-
date: Date to transform
-
masque: Date mask to apply as output
-
timezone: Time zone to use
-
language: Language to be used if necessary
-
Returns : Returns the date in the defined time zone and language
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
DEPRESSED: use str.to_datez and date_time.to_str
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
DEDUCTED: prefer the use of date_time.deducted (below)
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_time.deducted
Purpose: Deducts a date from another date by specifying a pre-defined function.
4 arguments:
-
Date : Valid date Timezone: Time zone to use Language: language to be used if applicable Type: type of date
Possible type
Start Month End of month Start of Week End of Week Start of Quarter End of Quarter Start Semester End of Semester Start of Year End of Year
Returns a value of *type Date.
| date_time.deducted(12/02/2020, End Month). RETURNS 29/02/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 constants
date. tomorrow
DEPRESSED: prefer the use of date_time.tomorrow_of (below)
Purpose: Returns the date of the day after the requested day
date_time.tomorrow_of
Objective: Returns the date of the day after the requested day, at midnight in the requested time zone.
Date: date of the requested day Timezone: time zone to use
date_time.today
Objective: Returns the current day’s date, at midnight in the requested time zone.
Timezone: time zone to use
date.yesterday
DEPRESSED: prefer the use of date_time.yesterday_of (below)
Objective: Returns the date of the day before the requested day
date_time.yesterday_of
Objective: Returns the date of the day before the requested day, at midnight in the requested time zone.
Date: date of the requested day Timezone: time zone to use
date_time.yesterday
Purpose: Returns yesterday’s date at midnight in the requested time zone.
Timezone: time zone to use
date.week_start
DEPRESSED: prefer the use of date_time.week_start_of (below)
Objective: Returns the start date of the current week
date_time.week_start_of
Objective: Returns the start date of the week on the requested day, at midnight in the requested time zone and for the relevant regional parameter
3 arguments:
Date: date of the requested day Timezone: time zone to be used Pays/Language: regional parameter concerned
date_time.week_start
Purpose: Returns today’s week start date, at midnight in the requested time zone and for the relevant regional parameter.
2 arguments:
Timezone: time zone to be used Pays/Language: relevant regional parameter
date.week_end
PREFERRED: prefer the use of date_time.week_end_of (below)
Objective: Returns the end date of the current week
date_time.week_end_of
Objective: Returns the end date of the week on the requested day, at midnight in the requested time zone and for the relevant regional parameter
3 arguments:
Date: date of the requested day Timezone: time zone to be used Pays/Language: regional parameter concerned
date_time.week_end
Objective: Returns the end date of today’s week, at midnight in the requested time zone and for the relevant regional parameter.
2 arguments:
Timezone: time zone to use Pays/Language: relevant regional parameter
date.month_start
DEPRESSED: prefer the use of date_time.month_start_of (below)
Objective: Returns the start date of the current month
date_time.month_start_of
Purpose: Returns the start date of the month of the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.month_start
Objective: Returns the start date of today’s month, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date.month_end
DEPRECIATED: prefer the use of date_time.month_end_of (below)
Objective: Returns the end date of the current month
date_time.month_end_of
Purpose: Returns the end date of the month of the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.month_end
Purpose: Returns the end date of the current month, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date.quarter_start
DEPRECIATED: prefer the use of date_time.quarter_start_of (below)
Objective: Returns the start date of the current quarter
date_time.quarter_start_of
Purpose: Returns the quarter start date of the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.quarter_start
Objective: Returns the quarter start date of the current day, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date.quarter_end
PREFERRED: prefer the use of date_time.quarter_end_of (below)
Objective: Returns the end date of the current quarter
date_time.quarter_end_of
Purpose: Returns the end date of the quarter for the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.quarter_end
Objective: Returns the quarter end date of the current day, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date_time.semester_start_of
Objective: Returns the semester start date of the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.semester_start
Purpose: Returns the semester start date of the current day, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date_time.semester_end_of
Objective: Returns the end date of the semester on the requested day, at midnight in the requested time zone.
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.semester_end
Objective: Returns the semester end date of the current day, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date.year_start
DEPRESSED: prefer the use of date_time.year_start_of (below)
Objective: Returns the start date of the current year
date_time.year_start_of
Objective: Returns the start date of the year of the requested day, at midnight in the requested time zone
2 arguments:
Date: date of the requested day Timezone: time zone to use
date_time.year_start
Objective: Returns the start date of the year of the current day, at midnight in the requested time zone.
1 argument:
Timezone: time zone to use
date.year_end
DEPRECIATED: prefer the use of date_time.year_end_of (below)
Objective: Returns the end date of the current year
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 |
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
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
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.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 |
DataChain Specific Functions
dc.ranking
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
Formula dc.rule expects 3 arguments
Argument 1: Rule built with DataChain QueryBuider
Argument 2: Value returned by the formula if the rule returns True
Argument 3: Value returned by the formula if the rule returns False
Block containing information about the current formula
Creation steps:
Click to display the list of possible arguments
Choose Rule to open the rules popup
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 
Click on validate to close the popup
Specify the character string to return if the rule returns TRUE
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
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
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