List Functions#
In Spectrum multiple values can be combined to form a list. List functions allow you to operate on a complete list, add values to a list, reorder the elements of a list, or remove a value from a list.
The list functions in Spectrum are:
ADD#
Syntax#
ADD(<list>;<any>)
Description#
Adds a value or values into a list.
The value(s) being added must be of the same data type as in the list.
Examples
| List column | Data column | ADD returns |
|---|---|---|
| [4,5] | 8 | [4, 5, 8] |
| [true, true, false] | true | [true, true, false, true] |
| [Mar 22 2008, Nov 03 2011] | Apr 13 2012 | [Mar 22 2008, Nov 03 2011, Apr 13 2012] |
| [blue, brown, red, green] | apple | [blue, brown, red, green, apple] |
COMPRESS#
Syntax#
COMPRESS(<list>)
Description#
Removes all null values from a list.
Examples
| Column1 | COMPRESS returns |
|---|---|
| [4, 18, 36, null, 95] | [4, 18, 36, 95] |
| [33.7, 82.5, 126.02, 133.4, null] | [33.7, 82.5, 126.02, 133.4] |
| [Tue Aug 02 15:55:03 CEST 2011, null, Fri Oct 15 04:34:34 CEST 2010] | [Tue Aug 02 15:55:03 CEST 2011, Fri Oct 15 04:34:34 CEST 2010] |
| [true, false, false, null, true] | [true, false, false, true] |
| [Mike, John, Charles, null, Rob] | [Mike, John, Charles, Rob] |
EXPAND#
Syntax#
EXPAND(<list>)
Description#
Expands a list, returning each element as an individual record.
Examples
| Column1 | EXPAND returns |
|---|---|
| [4, 18, 36, 72, 95] | 4 |
| 18 | |
| 36 | |
| 72 | |
| 95 |
INDEXOF#
Syntax#
INDEXOF(<list>;<element any>)
Description#
Returns (as an integer) the index of the first occurence of an element in a list counting from zero (0) or -1 if the element isn't found.
Examples
| Column1 | Searching for element | INDEXOF returns |
|---|---|---|
| [1, 2, 3, 4, 5] | 4 | 3 |
| [apple, banana, peach, orange, kiwi] | peach | 2 |
| [11, 12, 13, 14, 15] | 17 | -1 |
INTERSECTION#
Syntax#
INTERSECTION(<list>;<list>;...)
Description#
Returns the intersection of all list arguments. The resulting list contains only the elements found in all the list arguments and doesn't contain any duplicate elements.
Example
INTERSECTION(Column1;Column2;Column3;Column4)
| Column1 | Column2 | Column3 | Column4 | INTERSECTION returns |
|---|---|---|---|---|
| [1, 2, 3, 4] | [3, 4, 5] | [1, 2, 3, 4, 5] | [3, 3, 4, 4] | [3, 4] |
ISEMPTY#
Syntax#
ISEMPTY(<list>)
Description#
This function checks to see if a list contains only empty strings and returns a boolean value.
If the list is empty the function returns true.
If the list is not empty the function returns false.
Examples
| Data | ISEMPTY() returns |
|---|---|
| [1, 2, 3, 4] | false |
| [] | true |
| [1, 2, null, null] | false |
LIST#
Syntax#
LIST(<any>)
Description#
Builds a list value out of its arguments.
All values must be of the same data type.
Examples
LIST(#RawData!Column1; #RawData!Column2; #RawData!Column3; #RawData!Column4 )
| Column1 | Column2 | Column3 | Column4 | LIST returns |
|---|---|---|---|---|
| 2 | 2 | 2 | 2 | [2, 2, 2, 2] |
| 3 | 5 | 7 | 9 | [3, 5, 7, 9] |
| 24 | 56 | 87 | 3 | [24, 56, 87, 3] |
| 243 | 43 | 993 | 12 | [243, 43, 993, 12] |
LISTELEMENT#
Syntax#
LISTELEMENT(<list>;<integer>)
Description#
Returns the given element of a list.
LISTELEMENTautomatically lists all elements of the listLISTELEMENTallows to choose blocks of elements to list
Examples
In this example the function is looking for the third element in the referenced list. Elements are stored following order: [0, 1, 2, 3, ...]
LISTELEMENT(#RawData!Column1;3)
| Column1 | LISTELEMENT returns |
|---|---|
| [2, 2, 2, 2] | 2 |
| [3, 5, 7, 9] | 9 |
| [24, 56, 87, 3] | 3 |
| [243, 43, 993, 12] | 12 |
| [10, 5, 178, 23, 58] | 23 |
RANGE#
Syntax#
RANGE(<number>;<number>)
Description#
Returns in the form of a list, integers ranging in numerical order from the first value (From) and ending with last value (To) .
If the first value (From) is higher than the second value (To) the result is an empty list.
Examples
| Column1 | Column2 | RANGE returns |
|---|---|---|
| 1 | 3 | [1, 2, 3] |
| 3 | 1 | [] |
| 27 | 35 | [27, 28, 29, 30, 31, 32, 33, 34, 35] |
| 75.2 | 54.9 | [] |
| 5.4 | 20.7 | [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20] |
REMOVE#
Syntax#
REMOVE(<list>;<any>)
Description#
Removes a value from a list.
When removing a string value from a list that the value must be surrounded by quotation marks "<string example>"
Examples
In this example we want to remove the value of 5 from our list in column1.
REMOVE(#RawData!Column1;5)
| Column1 | REMOVE returns |
|---|---|
| [5, 6, 7, 8] | [6, 7, 8] |
| [2, 3, 4, 5] | [2, 3, 4] |
| [7, 6, 5, 4] | [7, 6, 4] |
| [243, 43, 993, 12] | [243, 43, 993, 12] |
REMOVEALL#
Syntax#
REMOVEALL(<list>;<list>;...)
Description#
Removes all elements from one list that exist in a specified list or lists.
Examples
REMOVEALL(#Column1;#Column2)
| Column1 | Column2 | REMOVEALL returns |
|---|---|---|
| [1, 2, 3, 4] | [3, 4, 5, 6] | [1, 2] |
| [3, 4, 5, 6] | [1, 2, 3, 4] | [5, 6] |
| [7, 2] | [7, 6, 5, 4] | [2] |
| [243, 43, 12] | [243, 43, 993, 12] | [] |
REVERSE#
Syntax#
REVERSE(<list>)
Description#
Returns the selected list in the reverse order.
Examples
| Column1 | REVERSE returns |
|---|---|
| [red, blue, green, yellow] | [yellow, green, blue, red] |
| [purple, green, orange, black] | [black, orange, green, purple] |
| [1, 2, 3, 4] | [4, 3, 2, 1] |
| [true, true, true, false] | [false, true, true, true] |
SIZE#
Syntax#
SIZE(<list>)
Description#
Returns the the number of elements in a list.
Examples
| Example lists | SIZE returns |
|---|---|
| [1, 2, 3] | 3 |
| [234, 234, 530] | 3 |
| [red, yellow, blue, green, orange, purple] | 6 |
| [true, false, false, true , false, false, true , ] | 7 |
SORT#
Syntax#
SORT(<list>)
Description#
Returns the selected list in numerical or alphabetical order.
Examples
| Example | SORT returns |
|---|---|
| [red, blue, green, yellow] | [blue, green, red, yellow] |
| [purple, green, orange, black] | [black, green, orange, purple] |
| [34, 12, 38, 19] | [12, 19, 34, 38] |
| [true, true, true, false] | [false, true, true, true] |
SUBLIST#
Syntax#
SUBLIST(<list>;<integer>)
Description#
Returns the selected list starting from a specified element.
Examples
In this example the function returns the list in column1 starting from the element in position 2. Elements are stored following order: [0, 1, 2, 3, ...]
SUBLIST(#RawData!Column1;2)
| Column1 | SUBLIST returns |
|---|---|
| [2, 2, 2, 2, 4] | [2, 2, 4] |
| [green, black, orange, red] | [orange, red] |
| [24.3, 56.3, 87.4, 3.3, 11.7, 63.8, 34.7] | [87.4, 3.3, 11.7, 63.8, 34.7] |
| [true, false, false, true, true] | [false, true, true] |
TOJSON#
Syntax#
TOJSON(<list>)
Description#
Returns the selected list as a JSON array.
Examples
| Column1 | TOJSON returns |
|---|---|
| [red, blue, green, yellow] | ["red","blue","green","yellow"] |
| [34, 12, 38, 19] | [34,12,38,19] |
| [true, true, true, false] | [true,true,true,false] |
TOKENIZELIST#
Syntax#
TOKENIZELIST(<string>;<Separator string>;<number>)
Description#
Tokenizes text returning a list of values or a specified element as a string. A blank string returns a blank list item.
Examples
| Column1 | Example TOKENIZELIST function | TOKENIZELIST returns |
|---|---|---|
| [a b c d e f] | [TOKENIZELIST(#RawData!Column1;" ")] | [[a, b, c, d, e, f]] |
| [Spectrum is great!] | [TOKENIZELIST(#RawData!Column1;" ")] | [[Spectrum is, great!]] |
| [Datameer-is-great!] | [TOKENIZELIST(#RawData!Column1;"-")] | [[Spectrum is, great!]] |
| [a b c d e f] | [TOKENIZELIST(#RawData!Column1;" ";3)] | d |
If the separator value is followed by the same separator value, an empty value is returned in the list.
| Column1 | Example TOKENIZELIST function | TOKENIZELIST returns |
|---|---|---|
| a-b-c-d-e- -f | TOKENIZELIST(#RawData!Column1;"-") | [[a, b, c, d, e, , |
| aaa | TOKENIZELIST(#RawData!Column1;"a") | [, , ,] |
UNIQUES#
Syntax#
UNIQUES(<list>)
Description#
Returns unique values from a selected list.
Examples
| Reverse example | UNIQUES returns |
|---|---|
| [red, green, green, yellow] | [red, green yellow] |
| [12, 12, 12, 12, 12, 12, 12, 12] | [12] |
| [Matt, Becky, Frank, Matt] | [Matt, Becky, Frank] |
| [12.6, 12.4, 12.7, 12.9] | [12.6, 12.4, 12.7, 12.9] |