Skip to content

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.

  • LISTELEMENT automatically lists all elements of the list
  • LISTELEMENT allows 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]