# Constant Tables

Constant tables are useful for organizing somewhat complex static information in your Ruleset.

## Creating a Constant Table

It's easy to get started:

1. Create a new Constant named *Levels*.
2. Change the **Constant type** from *Value* to *Table*.

Now, let's add some data.

1. Change the key name to *Low*.
2. Change the value to *0*.
3. Press Enter.
4. Enter *Medium* 
5. Press Tab.
6. Enter *50*.
7. Press Enter.
8. Enter *High* and *100* for the key and value, respectively.

You should now end up with a table with the following structure:

| Key    | value |
| ------ | ----- |
| Low    | 0     |
| Medium | 50    |
| High   | 100   |

#### Using your Constant table

Let's use the *Levels* constant table in a rule.

1. Create a new Rule named *LevelTest*
2. In Expression type: `Levels.`

Notice how it tries to auto-complete with `Low`, `Medium`, `High` and `includes`. We'll cover `includes` in a moment.

1. Change the Expression to: `Levels.Medium`.
2. Save the Ruleset.
3. Scroll to the top and click on **TRY IT OUT!**

Let's check what *LevelTest* evaluates to.

1. Click on **EXECUTE**.

If all is well, it should return *50* for LevelTest.

#### Expanding the table with extra values

A constant table can include more than one value per key. Let's expand our *Levels* table with ranges!

1. Open the *Levels* constant again.
2. Click on **ADD VALUE**.
3. Change *value* into *from*.
4. Change *value2* into *to*.
5. Add values to the *to* column so the table matches this:

| Key    | from | to  |
| ------ | ---- | --- |
| Low    | 0    | 49  |
| Medium | 50   | 99  |
| High   | 100  | 999 |

Now, using this constant table is quite simple.

1. Go back to the *LevelTest* rule.
2. Change the Expression to: `Levels.Medium.from`
3. Save the Ruleset and return to the Sandbox.
4. Execute the Ruleset again.

Once again, you should see *50* as the value for *LevelTest*.

Instead of using *50* and *99* directly in our code, we can use them by writing *Levels.Medium.from* and *Levels.Medium.to*. For instance, we may have a rule that checks if a value is "Low", "Medium" or "High".

1. Add a new Input named `value`
2. Create 3 rules named *IsLow*, *IsMedium,* and *IsHigh* with the following expressions: 
   1. IsLow: `value >= Levels.Low.from && value <= Levels.Low.to`&#x20;
   2. IsMedium: `value >= Levels.Medium.from && value <= Levels.Medium.to`&#x20;
   3. IsHigh: `value >= Levels.High.from && value <= Levels.High.to`
3. Try the ruleset out in the Sandbox with different values for `value`.

#### CSV

Instead of manually creating a Constant table, you can also drag and drop a CSV file on it!

The following rules apply to CSV files:

* The file should have a header row
* Each row should have values separated by a comma
* If a value represents a number, do not add quotes
* If a value represents a string, add quotes to the value

An example file:

```
key,   value1, value2
"aaa",  2,      5
"bbb",  3,      6
"ccc",  8,      10
```

**Usage in code examples:**

If the Constant table you've dragged the CSV file on is named *MyData*, you may access it like this: `MyData.ccc.value2` to get the value `10` in the example above.

To check if a key exists: `'bbb' in MyData` -or- `MyData.includes('bbb')`.

## Constant Table language reference

A constant table has several functions you can use:

| Function                   | Description                                                                                                                |
| -------------------------- | -------------------------------------------------------------------------------------------------------------------------- |
| includes(key)              | Returns if the given key exists in the table (`true` or `false`)                                                           |
| get(key)                   | Returns a value associated with a key in the table.                                                                        |
| find(predicate)            | Finds and returns a row in the table based on the given predicate.                                                         |
| findKey(predicate)         | Finds and returns a key based on the given predicate. Can be used for reverse lookups.                                     |
| intersect(key, headerName) | Finds the intersect of a key and a header name and returns the value found in that cell.                                   |
| lookup(value)              | A special lookup function that is useful for multi-column tables. Can be used to use a constant table as a decision table. |

Besides functions there are also some properties:

| Property    | Description                                    |
| ----------- | ---------------------------------------------- |
| rows        | An array containing the rows                   |
| keys        | An object containing all the keys              |
| keyNames    | An array of strings containing all the keys    |
| headers     | An object containing all the headers           |
| headerNames | An array of strings containing all the headers |

### includes()

`includes(key)`

As seen in the previous example, you can check if a key exists in a Constant table by using the `in` keyword or the `includes()` method. Checking for the existence of a key is very useful if you're using a Constant table as a checklist.

For instance, you could have a Constant table named *AllowedNumbers* without any values that looks like this:

| Key |
| --- |
| 1   |
| 5   |
| 43  |
| 667 |

Assume we have an Input named *A*. We could have a Rule named `AOK` that checks if *A* is an allowed number: AOK: `A in AllowedNumbers` or AOK: `AllowedNumbers.includes(A)`

The `in` key word and `includes()` method work regardless if your Constant table has value columns or not.

### get()

This can be used to get a value from a constant table. Its result depends on the structure of the table.

`get(key)`

If the table only has a key column and no header columns, get will simply return the key itself (if it exists)

Let's assume this Constant is named "ExampleTable1"

| Key    |
| ------ |
| Low    |
| Medium |
| High   |

`ExampleTable1.get("Medium")` gives us `"Medium"`.

If the table is structured with just a single header column like this below, it will return the value.

Let's assume this Constant is named "ExampleTable2"

| Key    | value |
| ------ | ----- |
| Low    | 1     |
| Medium | 5     |
| High   | 10    |

`ExampleTable2.get("Medium")` gives us `5`.

If the table is structured with multiple header columns, it will return an object instead.

Let's assume this Constant is named "ExampleTable3"

| Key    | from | to |
| ------ | ---- | -- |
| Low    | 1    | 4  |
| Medium | 5    | 7  |
| High   | 10   | 15 |

`ExampleTable3.get("Medium")` gives us the object `{ from: 5, to: 7 }`.

### find()

This can be used to find and return an entire row from a constant table.

`find(predicateFunction)`

If we refer to the ExampleTables from the `get()` section we will find that the table's `rows` are structured differently depending on the table's structure:

`ExampleTable1.find(row => row.key == "Medium")` gives us the object `{ key: "Medium", value: "Medium" }`.&#x20;

Of course this isn't very useful, as we could have gotten the data we want easier by using the `get()` method in this case.

`find()` is more useful when used on tables that have header columns:

`ExampleTable2.find(row => row.value > 2)` gives us the object `{ key: "Medium", value: 5 }`.&#x20;

This is because it finds and returns the first row which value is higher than 2, in this case it is the Medium row.

If the table has more than 1 header column, the `value` property of the object passed in the predicate function changes to `values`:

`ExampleTable3.find(row => row.values.to == 7)` gives us the object:&#x20;

```javascript
{
  key: "Medium",
  values: {
    from: 5,
    to: 7
  }
}
```

### findKey()

This works similar to `find()` but it returns only the key.

`findKey(predicateFunction)`

Using the example tables from `get()`:

`ExampleTable1.findKey(row => row.key == "Medium")` gives us "Medium"

`ExampleTable2.findKey(row => row.value > 6)` gives us "High"

`ExampleTable3.findKey(row => row.values.to == 4)` gives us "Low"

### intersect()

This is a very useful function to get the value of a cell where a key row and a header column intersect.

`intersect(key, header, keyRoundDirection='down', headerRoundDirection='down')`

When you have a constant table that consists of numeric keys and header names, `intersect()` will intelligently pick the most appropriate cell, even when the given values are between two values or even outside the table.

Let's define a table named "ExampleTable4":

<table><thead><tr><th>Key</th><th>0</th><th>0.5</th><th data-type="number">1</th></tr></thead><tbody><tr><td>0</td><td>1</td><td>2</td><td>3</td></tr><tr><td>0.5</td><td>4</td><td>5</td><td>6</td></tr><tr><td>1</td><td>7</td><td>8</td><td>9</td></tr></tbody></table>

`ExampleTable4.intersect(0, 1)`  this will give us `3` as that is the cell that has key 0 and header 1.

`ExampleTable4.intersect(0.5, 0.5)` will give us `5`, the center cell.

#### **Interpolation**

Suppose we work with user input in our Ruleset and the given key and header don't really match any keys or columns. `intersect()` will give us the best "guess":

`ExampleTable4.intersect(0.25, 0.25)` will give us `1`. This is because the key and header are `0.25` and these are not exactly found in the table, but the nearest cell is `1`.&#x20;

By default these interpolations round 'down', but this can be overridden:

`ExampleTable4.intersect(0.25, 0.25, 'up')` gives us `4`. This third parameter is used to instruct the key-rounding.

If we also want the header-rounding to be 'up', we will have to use a fourth parameter to do so:

`ExampleTable4.intersect(0.25, 0.25, 'up', 'up')` gives us `5`.

If we only want to use 'up' rounding on the header, we can do that as follows:

`ExampleTable4.intersect(0.25, 0.25, 'down', 'up')` gives us `2`.&#x20;

#### **Extrapolation**

It's possible that a given header and key fall beyond the range of the table entirely.

`ExampleTable4.intersect(-1, -1)` gives us `1`, because that's as far as the table's boundaries will allow. Similarly:

`ExampleTable4.intersect(2, 2)` gives us `9`, because that's as far as the table can go in the bottom-right direction.

### lookup()

This is used to find a value in a table by looking at one or more header columns. Its result is the value in either the final column or a given column.

`lookup(value)`

`lookup(value, headerName)`

`lookup(value, headerName, resultHeaderName)`

`lookup(multiValueObject)`

`lookup(multiValueObject, resultHeaderName)`

`lookup(multiValueObject, resultHeaderArray)`

Keys do not matter in this case and `lookup()` works on tables that are structured in a particular way.

Values can also be expressions like `< 0` or `>= 100` instead of numeric values.

Let's start by defining a table named "ExampleTable5":

| Key | age  | result |
| --- | ---- | ------ |
| 1   | < 51 | 10     |
| 2   | 51   | 11     |
| 3   | 52   | 12     |
| 4   | >52  | 15     |

The most simple syntax is:

`ExampleTable5.lookup(51)` - this gives us `11`. This is because an exact match is found in the "age" column for `51` , and so its corresponding value in the result column is returned. By default, the final column is always used as the result column. It does not matter what the name of this header is.

You may be wondering what the purpose of the `< 51` and `>52` rows are. These are used to define a value for when the age is in a certain range.

This will save you lots of work because you won't have to create rows for every age up to 51 and every age beyond 52 manually! Moreover, this keeps the table shorter and more concise.

`ExampleTable5.lookup(18)` - gives us 10, as it's `< 51` (i.e., "less than 51")

`ExampleTable5.lookup(96)` - gives us 15, as it's `> 52` (i.e., "greater than 52")

Let's define a slightly more complicated table named "ExampleTable6":

| Key | age            | code | result |
| --- | -------------- | ---- | ------ |
| 1   | < 18           | a    | 1      |
| 2   | >= 18 AND < 50 | b    | 2      |
| 3   | >= 50          | c    | 3      |

Looking at the second row, we will see that more complex logic is also possible.

`ExampleTable6.lookup(16)` - gives us `1`, the first row in the column `age` that matches our given value.

`ExampleTable6.lookup(30)` - gives us `2`.

`ExampleTable6.lookup(89)` - gives us `3`.

You'll notice that the first header column is used to look up the value.

Now what if we actually don't care about the age column, but want to look up by `code`? We can do that as follows:

`ExampleTable6.lookup("b", "code")` - gives us `2`. We looked up the value "b" in the `code` column.

But what if we want to look up by `age`, but we want to have it return the corresponding `code` instead of `result`? This can be done by using a third parameter:

`ExampleTable6.lookup(25, "age", "code")`- gives us "b".

If we want to be super-specific, we can also lookup values in multiple header columns. The syntax changes for this slightly.

Let's say we want to look up the result of someone who's 25 and has code "b":

`ExampleTable6.lookup({ age: 25, code: "b"})` - gives us `2`.

A result column can also be given:

`ExampleTable6.lookup({ age: 25 }, "code")` - gives us "b".

So far, all of our examples were cases where a value could be found, but what happens if it can't?

`ExampleTable6.lookup({ age: 92, code: "a"})` - gives us `null`. This is because this combination of values can not be found in the table.

### rows

This property returns a constant table's rows, allowing you to programmatically work with it.

`ExampleTable1.rows` (the one with no header columns) - gives us:

```javascript
[
  { key: "Low", value: "Low" },
  { key: "Medium", value: "Medium" },
  { key: "High", value: "High" }
]
```

`ExampleTable2.rows` - gives us:

```javascript
[
  { key: "Low", value: 0 },
  { key: "Medium", value: 5 },
  { key: "High", value: 10 }
]
```

`ExampleTable3.rows` - gives us:

```javascript
[
  { key: "Low", values: { from: 0, to: 10 } },
  { key: "Medium", values: { from: 11, to: 20 } },
  { key: "High", values: { from: 21, to: 30 } }
]
```

### keys

This property returns an object containing all the keys.

`ExampleTable1.keys` - gives us:

```javascript
{ 
  Low: "Low", 
  Medium: "Medium", 
  High: "High" 
}
```

(The same object is returned for `ExampleTable2` and `ExampleTable3`.)

### keyNames

This property returns an array with all the keys in it.

`ExampleTable1.keyNames` - gives us: `[ "Low", "Medium", "High" ]`

(The same array is returned for `ExampleTable2` and `ExampleTable3`.)

### headers

This property returns an object containing all the header column names.

`ExampleTable1.headers`- gives us: `{}` (Because it has no header columns)

`ExampleTable2.headers`- gives us: `{ value: "value" }`

`ExampleTable3.headers`- gives us: `{ from: "from", to: "to" }`

### headerNames

This property returns an array with all the header column names in it.

`ExampleTable1.headerNames`- gives us: \[] (Because it has no header columns)

`ExampleTable2.headerNames`- gives us: \[ `"value" ]`

`ExampleTable3.headerNames`- gives us: \[`"from", "to" ]`