join

join, aggregator function

def process join(value: text; delimiter: text): text

Concatenates all text values in the group, separated by the specified delimiter. The aggregation is performed in the order specified by the sort option.

• value: The text values to be concatenated.
• delimiter: The text to insert between each pair of concatenated values.

Examples

Basic usage

table T = with
  [| as Id, as Value |]
  [| "a",   "apple"  |]
  [| "b",   "banana" |]
  [| "c",   "cherry" |]

result = join(T.Value; ", ") sort T.Id

// "apple, banana, cherry"
show scalar "Joined values" with result

Different delimiters for different groups

table Products = with
  [| as Category, as Product |]
  [| "fruit",     "apple"    |]
  [| "fruit",     "banana"   |]
  [| "fruit",     "cherry"   |]
  [| "veg",       "carrot"   |]
  [| "veg",       "potato"   |]

table Categories[cat] = by Products.Category
Categories.Delimiter = if cat == "fruit" then ", " else " & "

Categories.ProductList =
  join(Products.Product; Categories.Delimiter) sort Products.Product

show table "Product lists by category" with
  cat
  Categories.ProductList

This produces the following table:

Filtered aggregation

table T = with
  [| as Id, as Value |]
  [| "a",   "apple"  |]
  [| "b",   ""       |]
  [| "c",   "cherry" |]

// Only join non-empty values.
result = join(T.Value; ", ") when (T.Value != "") sort T.Id

// "apple, cherry"
show scalar "Non-empty values" with result

Remarks

The join function requires the sort option to specify the order in which values will be concatenated. Since the result of join depends on the order, it must be specified explicitly.

Unlike some other aggregators, join takes a group argument (after the semicolon) to specify the delimiter to use. This allows for different delimiters to be used for different groups.

The join function works with empty texts, which will be included in the output unless filtered out.

Note that join can be implemented as a user-defined process in the following way:

def process myJoin(a : text; delimiter : text) with
  keep c = ""
  if c == ""
    c = a
  else
    c = "\{c}\{delimiter}\{a}"
  return c

Errors

Text values are limited to 256 characters in Envision. If this limit is exceeded, such as this:

table T = extend.range(100)

T.Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

limitExceeded = join(T.Value; ", ") sort T.N

show scalar "" with limitExceeded

it will yield an error such as this:

‘join(what;sep)’: overflow on value “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, xxxxxxxx…”

Recipes and best practices

1. Always specify sorting order: Since the order of concatenation matters, explicitly provide a sorting order using the sort option.

2. Filter empty values: Use the when option to filter out empty or unwanted values before joining.

3. Dynamic delimiters: Use different delimiters for different groups by passing a vector as the delimiter argument:

   table T = with
     [| as Group, as Value |]
     [| "A", "one"   |]
     [| "A", "two"   |]
     [| "B", "three" |]
     [| "B", "four"  |]
   
   table Groups[g] = by T.Group
   Groups.Delimiter = if g == "A" then ", " else "; "
   
   Groups.JoinedValues = join(T.Value; Groups.Delimiter) sort T.Value
   
   // |------------------|
   // | g | JoinedValues |
   // | A | one, two     |
   // | B | three; four  |
   // |------------------|
   show table "Dynamic Delimiters" with
     g
     Groups.JoinedValues
   

4. Text conversion: If you need to join non-text values, convert them to text first:

   table Numbers = with
     [| as N |]
     [| 1    |]
     [| 2    |]
     [| 3    |]
   
   result = join(text(Numbers.N); ", ") sort Numbers.N
   
   // "1, 2, 3"
   show scalar "Joined numbers" with result
   

See also

• concat
• sort
• when
• text