functionsValue lists (Arrays) allow the summarisation of different values in a list. A value list can contain values of different data types and also null .
| Valuation functions | Syntax | |
|---|---|---|
|
VALUES - ARRAY |
Creates a value list from transfered parameters.
Example: =VALUES("a", "b", "c") or =VALUES(1, 2+5, 4) |
VALUES(number value1; [number value1]; [number ...]) value1: a optional value which should be taken in the list. |
|
AREAS |
Creates a value list from the available data records of a component, which has implemented the interface DataBean. Those applies
to the following eva/3 Application Builder components:
If a statement is given with more components, then the value list will be created in the ranking of the component enumeration. Example: =AREAS([txt_personen_id]) Creates a value list from the database column which was defined with the property ControlSource in the component with the name txt_personen_id . =AREAS([txt_personen_id1], [txt_personen_id2]) Creates a value list from the database column from both named components. Advice: The data of the columns will be completely loaded in the memory for the creation of the value list. The function is not adapted for big tables! |
AREAS(variant reference1; [variant reference2]; [variant ...]]) Reference1: Reference to a component, from which a value list is to create. |
|
ISARRAY |
Checks if the transfered value is a value list. Returns the boolean constant true,
if the transfered value is a value list, false if not.
Example: =ISARRAY(VALUES("a", "b", "c")) with the result true. |
ISARRAY(values value) value: Value which is to ckeck for a value list. |
|
JOIN |
Creates a string from a value of a one- or n'th dimensional value list. At specification of a separator will furthermore the separator
be entered in the value list between every value und his following value .
Example: =JOIN(VALUES("a"; "b"); "_"]) with the result a_b. |
JOIN(values value list; [literal separator]) value list: Value list from which a string should be created. separator: Separator which should be entered betweenn the values of the value list. Default value is a space. |
|
SPLIT |
Creates a value list from a string, where the separator is used to appoint the borderlines of the entries of the value list.
Example: =SPLIT("Hallo World"; " "; -1;) creates a value list with the entries Hallo and World. |
=SPLIT(literal string; [literal separator]; [number ceiling]; [number mode]) String: A string which should be parted in a value list. Separator: Separator which is bordering the values of the string. Default value is a space. Ceiling: Number of values, which can be created maximal. Values which excess the limit will not be put in the value list. The Default value is -1 for the intake of all created values. Mode: Compare mode 0 for the bynary (upper-/lowercase noticing) compare mode or 1 for the text compare mode (upper-/lowercase ignoring). (Default value 1) |
|
FILTER |
Allows the reduce of a one- or n'th dimensional value list at the values which accord the filter.
Example: =FILTER(VALUES("A", "B"), "a") creates a value list with the entry A. |
=FILTER(values value list; literal filter value; [boolean inklusive]; [number mode]; [boolean regular expression]) Value list: The value list which is to filter. Filter value: A string which contains the filter value. Inklusive: A boolean constant which decides if all correct values all not correct values are taken or. Has this parameter the value true, then all correct values will be taken in the filtered value list. Otherwise all not correct values will be taken. The default value is true. Mode: Compare mode 0 for the bynary (upper-/lowercase noticing) compare mode or 1 for the text compare mode (upper-/lowercase ignoring). (Default value 1) Regular expression: Allows the use of the parameter Filter value as regular expression. If Regular expression is transfered the value true, then the filter value will be used as regular expression. If the regular expression results the value of the value list true, then the value will be taken in the filtered value list. If Regular expression is transfered the value false , then the appeareance of the filter value will be proved in the entry of the value list. |
|
UBOUND |
Returns the position of the last value of the value list. The first position is acquired from 0. The returned value is always a numeric value.
Example: =UBOUND(VALUES("a", "b")) with the result 1. |
=UBOUND(values value list) Value list: The value list which position of the last entry should be determine. |
| UNION |
Merges the two array given with the arguments first and second.
Example: =UNION(VALUES(4,1,7,3), VALUES(1,9,3,2)) with the result VALUES(4,1,7,3,1,9,3,2). |
=UNION(values first, values second, ) first: An array to be merged with the array given with the parameter second. second: An array to be merged with the array given with the parameter first. |
|
DUPLICATES |
Determine all entries existing in both given arrays and creates a new array which contains all duplicate entries.
Example: =DUPLICATES(VALUES(4,1,7,3), VALUES(1,9,3,2)) with the result VALUES(1, 3). |
=DUPLICATES(values first, values second, ) first: Array to be searched for entries exiting in the array given with the second parameter. first: Array to be searched for entries exiting in the array given with the second parameter. second: Array to be searched for entries exiting in the array given with the first parameter. |
|
DIFFERENCE |
Determine all entries that did not existing in both given arrays and creates a new one which contains all non redundant entries.
Example: =DIFFERENCE(VALUES(4,1,7,3), VALUES(1,9,3,2)) with the result VALUES(4, 9, 7, 2). |
=DIFFERENCE(values first, values second, ) first: Array to be searched for entries not exiting in the array given with the second parameter. second: Array to be searched for entries not exiting in the arry given with the first parameter. |
|
RESIZE |
Modifies the size of an existing value list. If the value list is smaller, than the overhanging values will be lost.
If the size is bigger, the rest of the value list will be filled with null values.
Example: =RESIZE(VALUES(4,1,7,3), 2) with the result VALUES(4, 1). |
=RESIZE(values Values; number size) Values: The values list which size should be changed. size: The new size for the value list. |
|
EXISTS |
Searches for a value within a one- or n'th dimensional values list. If the value is found, true will be returned.
If there is no value in the values list present, the return value is false.
Example: =EXISTS(VALUES(4,1,7,3), 2) with the result false. |
=EXISTS(values Values; literal Search value; [number Mode]; [boolean Regular expression]) Values: The values list to be searched. Search value: The value to be searched in the values list. Mode: Compare mode 0 for the bynary (upper-/lowercase noticing) compare mode or 1 for the text compare mode (upper-/lowercase ignoring). (Default value 1) Regular expression: Allows the use of the parameter Search value as regular expression. If Regular expression is transfered the value true, then the Search value will be used as regular expression. If the regular expression results the value of the value list true, the result is also true. |
|
APPEND |
Appends an entry at the end of a values list. The size of the values list will be increased by one.
Example: =APPEND(VALUES(4,1,7,3), 2) with the result VALUES(4,1,7,3, 2). |
=APPEND(values Values; variant Value) Values: The values list to be increaed. Value: The value to be appended at the end of the values list. |
|
PREPEND |
Prepends an entry at the beginning of a values list. The size of the values list will be increased by one.
Example: =PREPEND(VALUES(4,1,7,3), 2) with the result VALUES(2,4,1,7,3). |
==PREPEND(VALUES(4,1,7,3), 2)(values Values; variant Value) Values: The values list to be increaed. Value: The value to be appended at the beginning of the values list. |
|
PUT |
Inserts an entry into a value list at the specified index. If the value list has also an entry at the given index,
the entry will be overriden. If the index is specified beyond the value list, the value list will be extended. If
the index is a negative value, the entry will be inserted at the front of value list at the specified negative location.
The value list will be shifted by the index value.
Beispiel: =PUT(VALUES(4,1,7,3); 2; -2) with the result VALUES(2,null,4,1,7,3). =PUT(VALUES(4,1,7,3); 2; 0) with the result VALUES(2,1,7,3). |
=PUT(values Values; variant Value; number Index) Values: The values list where the Value should be inserted to. Value: The value to be inserted into the values list. Index: The index where the Value should be inserted into the values list. |
|
REVERSE |
Reverses the values of a value list.
Example: =REVERSE(VALUES(4,1,7,3)) with the result VALUES(3,7,1,4). |
=REVERSE(values Values) Values: The values list to be reversed. |
|
DISTINCT |
Removes duplicate entries from a values list.
Example: =DISTINCT(VALUES("1", "2", "2")) creates a value list with the entries VALUES("1", "2"). |
=DISTINCT(values value list; [number mode]) Value list: The value list which is to filter. Mode: Compare mode 0 for the bynary (upper-/lowercase noticing) compare mode or 1 for the text compare mode (upper-/lowercase ignoring). (Default value 1) |
|
EXTRACT |
Extracts the part of a value list which is defined between the start and the end argument.
Example: =EXTRACT(VALUES("1", "2", "3"), 0, 2) creates a value list with the entries VALUES("1", "2"). |
=EXTRACT(values value list; [number Start]; number End) value list: The values lis from which a range should be extracted. Start: Specifies the begin of the range to be extracted. (Default value is 0) End: Specifies the end of the range to be extracted. |
|
SHUFFLE |
Shuffles the values of a value list into a random order.
Example: =SHUFFLE(VALUES("1", "2", "3")). |
=SHUFFLE(values value list) value list: The values list to be shuffled. |
|
REMOVE |
Removes an entry of a value list. If the index is behind the last entry of the values list, no entry will be removed.
Example: =REMOVE(VALUES("1", "2", "3"), 1) creates a value list with the entries VALUES("1", "3"). |
=REMOVE(values value list; number Index) value list: The value list an entry should be removed. Index: Defines the location of the entry to be removed from the value list. The first entry starts with 0. |
|
RANDOMVALUE |
Extracts a random value from a value list.
Example: =RANDOMVALUE(VALUES("1", "2", "3")). |
=RANDOMVALUE(values value list) value list: The value list from where a random entry should be extracted. |
|
DETACHFIRST |
Removes the first entry from a value list.
Example: =DETACHFIRST(VALUES("1", "2", "3")) creates a value list with the entries VALUES("2", "3"). |
=DETACHFIRST(values value list) value list: The value list from which the first entry should be removed. |
|
DETACHLAST |
Removes the last entry from a value list.
Example: =DETACHLAST(VALUES("1", "2", "3")) creates a value list with the entries VALUES("1", "2"). |
=DETACHLAST(values value list) value list: The value list from which the last entry should be removed. |
|
CLEAR |
Removes all entries from a value list.
Example: =CLEAR(VALUES("1", "2", "3")) creates a value list with the entries VALUES(). |
=CLEAR(values value list) value list: The value list from which all entries should be removed. |
|
DIVIDE |
Divides the values of a value list lineary into many value lists. The restart of the dividsion are controlled with
the "Amount" parameter.
Example: =DIVIDE(VALUES(1, "yes", 2, "no", 3, "possibly"), 2) creates a two dimensional value list: VALUES(VALUES(1, 2, 3), VALUES("yes", "no", "possibly")). |
=DIVIDE(values value list; [number amount]) Value list: The value list to be divided. Amount: Specifies the number of value list, the values should be divided to. The default value is 2. |
|
CONSOLIDATE |
Merges any given value lists into a two dimensional new one. The new value list is composed from each entry of each given value list.
This function can be used if value pairs should be created from severeal value lists.
Example: =CONSOLIDATE(VALUES(1, 2, 3), VALUES("Jan", "John", "Johan")) creates a two dimensional value list: VALUES( VALUES(1, "Jan"), VALUES(2, "John"), VALUES(2, "Johan") ). |
=CONSOLIDATE(values Value list1; [values Value list2]; [values Value list...]) Value list: The value lists to be consolidated. |
|
SORT |
Sorts a value list. The values will be sorted by it's valence. Null values will be handled int his
valence like empty strings.
Example: =SORT(VALUES(1, 3, 2)) creates a value list: VALUES(1, 2, 3). |
=SORT(values Value list; [number Algorithm]; [number Direction]) Value list: The value list to be sorted. Algorithm: The sort algorithm to be used:
Direction: Ascending or descending direction:
|
|
MIN |
s. Aggregate function
Example: =MIN(VALUES(5,1,2,3)) with the result 1. |
|
|
MAX |
s. Aggregate function
Example: =MAX(VALUES(5,1,2,3)) with the result 5. |
|
|
AVG |
s. Aggregate function
Example: =AVG(VALUES(5,1,2,3)) with the result 2.75. |
|
|
SUM |
s. Aggregate function
Example: =SUM(VALUES(5,1,2,3)) with the result 11. |
|
|
COUNT |
s. Aggregate function
Example: =COUNT(VALUES(5,1,2,3)) with the result 4. |
|
|
FIRST |
s. Aggregate function
Example: =FIRST(VALUES(5,1,2,3)) with the result 5. |
|
|
LAST |
s. Aggregate function
Example: =LAST(VALUES(5,1,2,3)) with the result 3. |
|