Collections
Using Collections to dynamically query large data sets
Collections are similar to what Excel users would called a Lookup, and you'll use them to extract variables from large datasets based on the inputs you’ve already been given.
For example, using a collection, you'd look down the first column of a dataset to find your key in a series of ABI codes, and you might then pick one particular rating from a larger table.
An Example Collection
bob
30
true
sue
24
false
jack
40
true
Example formats
'collection.map(age)' // [30, 24, 40]
'collection.map(age).first()' // 30
'collection.map(age).last()' // 40
'collection.filter(age<30).map(age).first()' // 24
'collection.filter(is_male=true).filter(age>30).map(name)first()' // jackThe output must resolve to a value (e.g. number, string, boolean, date). Collection queries must start with the word 'collection'.
Using inputs from previous steps
The input value is wrapped in {{ }}.
For example, if the input is called customer_age and is equal to 30 in the quote, then:
'collection.filter(age<{{customer_age}}).map(age).first()' // 24
// resolves to
'collection.filter(age<30).map(age).first()' // 24
Methods for use in Collections
count()
count()Counts the number of items in an response. It can be used after a filter has been applied.
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.count()' // 3
'collection.filter(age<30).count()' // 1
'collection.filter(is_male=true).filter(age>25).count()' // 2min(), max(), mean(), range(), sum()
min(), max(), mean(), range(), sum()Finding the min number, max number, mean number, sum number and range between the two.
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.map(age).min()' // 24
'collection.map(age).max()' // 40
'collection.map(age).range()' // 16 i.e. 40 - 24
'collection.map(age).mean()' // 31.3333333333
'round(collection.map(age).mean(), 2)' // 31.33
'collection.map(age).sum()' // 94
map()
map()Returns an array of the selected column within a collection of items. You can not return an array but you can have it as a chained step to do work on a collection.
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.map(age)' // [30, 24, 40]
'collection.map(name)' // ["bob", "sue", "jack"]
// You must resolve the array to make an output
'collection.map(name).first()' // "bob"filter()
filter()Returns a filtered collection based on a columns criteria.
Filters will work on columns with strings, booleans and numbers. Filters can be chained.
You can also filter against value or empty/null data with the ~ flag
Example:
bob
30
true
brown
sue
24
false
jack
40
true
brown
'collection.filter(age>30).map(name)' // ["jack"]
'collection.filter(age<40).filter(is_male=true).map(name)' // ["bob"]
'collection.filter(age<40).filter(age>24).map(name)' // ["bob"]
'collection.filter(surname=brown~).count()' // 3 as the ~ returns empty cells too
// You must resolve the array to make an output
'collection.filter(age>30).map(name).first()' // "jack"
unique
uniqueReturns an array of the unique items from a column
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.unique(age).count()' // 3
'collection.unique(age).map(age).sum()' // 94
'collection.unique(is_male).count()' // 2first, last
first, lastReturns an first or last element of an array as a value. This is a common way to resolve a result.
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.map(age).first()' // 30
'collection.map(age).last()' // 40
'collection.filter(age<30).map(age).first()' // 24exists
existsChecks if property value exists i.e. not null, undefined, empty string or 0.
Example:
bob
30
true
sue
24
false
jack
40
true
'collection.filter(age>50).exists()' // "false"
'collection.filter(age<50).exists()' // "true"date, age
date, ageIf property is a date, then you can use date() to find a hour, date, month or year from the date string, or use age() to find the age since now in hours, days, months or years.
You can format a date from US or UK date formats.
Example:
bob
1993-12-12
true
12/12/1993
sue
1999-01-01
false
01/01/1999
jack
1983-11-11
true
11/11/1983
'collection.map(dob).first().date(YY)' // 1993
'collection.map(dob).first().date(MM)' // 12
'collection.map(dob).first().date(DD)' // 12
'collection.map(dob_format).first().date(UK)' // 1993-12-12
'collection.map(dob_format).first().date(US)' // 1993-12-12
'collection.map(dob).first().age(YY)' // 30
'collection.map(dob).first().age(MM)' // 360
'collection.map(dob).first().age(DD)' // 131800A second argument can be provided to point at a date in the object instead of using now.
'collection.map(dob).first().age(YY, "2020-01-01")' // 27Date and age work with arrays.
'collection.map(dob).age(YY)' // [ 30 , 24, 40 ]postcode
postcodeIf property is a string postcode, then you can find the area and sector.
Example:
bob
1993-12-12
SW1A1AA
sue
1999-01-01
L201AR
jack
1983-11-11
M123RT
'collection.map(postcode).first().postcode(area)'); // "SW"
'collection.map(postcode).first().postcode(sector)'); // "SW1A"
'collection.map(postcode).postcode(area)'); // ["SW", "L", "M"]regex
regexIf property is a string, then you can use regex() to parse a regular expression against the string and return the first match.
Example:
'collection.map(postcode).first().regex(^[A-Za-z]{2}|^[A-Za-z]{1})'); // W
'collection.map(postcode).first().regex(^[A-Za-z]{2}|^[A-Za-z]{1})'); // POLast updated