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.

A completed Collection step

An Example Collection

name	age	is_male
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()' // jack

The 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()

Counts the number of items in an response. It can be used after a filter has been applied.

Example:

name	age	is_male
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()' // 2

min(), max(), mean(), range(), sum()

Finding the min number, max number, mean number, sum number and range between the two.

Example:

name	age	is_male
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()

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:

name	age	is_male
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()

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:

name	age	is_male	surname
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

Returns an array of the unique items from a column

Example:

name	age	is_male
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()' // 2

first, last

Returns an first or last element of an array as a value. This is a common way to resolve a result.

Example:

name	age	is_male
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()' // 24

exists

Checks if property value exists i.e. not null, undefined, empty string or 0.

Example:

name	age	is_male
bob	30	true
sue	24	false
jack	40	true

'collection.filter(age>50).exists()' // "false"
'collection.filter(age<50).exists()' // "true"

date, age

If 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:

name	dob	is_male	dob_format
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)' // 131800

A 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")' // 27

Date and age work with arrays.

'collection.map(dob).age(YY)' // [ 30 , 24, 40 ]

postcode

If property is a string postcode, then you can find the area and sector.

Example:

name	dob	postcode
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

If 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})'); // PO