✎ ego_script [Dinamica EGO]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Manipulating Tables and Lookup Tables ======

===== Overview =====

This page covers the functors and techniques used to read, iterate over, and restructure Tables and Lookup Tables once they exist. See [[table_type|Table Type]] and [[lookup_table_type|Lookup Table Type]] for the general format of these two types — column naming, key marking, and type inference — which this page assumes as background.

===== Tuple Type =====

A **Tuple** is a sequence of table cells, most often used to represent the key (or full set of keys) identifying a table row. Tuple elements are ordered by column index and have no names of their own — a Tuple by itself doesn't say which column each element belongs to; that mapping only exists in the context of the table it's being used against.

A Tuple literal uses bracket syntax — ''[2007]'' for a single-element Tuple, ''[2007, "Boston"]'' for several at once, the same list syntax a Table or Lookup Table literal uses. This bracket form is required when writing a Tuple as a literal constant: automatic conversion between types, such as a Real value becoming a one-element Tuple, only happens when a value is **connected** from one port to another — a variable reference, or a functor call inlined directly into the argument — never when a literal constant is parsed straight into a port (see [[ego_script#constants|Constants]] for this rule stated generally, beyond just Tuples). Each port's literal syntax is parsed by that type's own parser, which only accepts its own type's literal form; a bare ''2007'' fails wherever a Tuple is expected, even though a Real //value//, once connected, converts to a Tuple automatically. Binding a Tuple literal to a reusable name goes through the [[Tuple]] functor, the same way [[Table]] and [[Lookup Table]] wrap their own literals.

[[Add Tuple Value]] serves a different case: appending one element — often a connected variable, rather than a literal — to a Tuple that already exists. Every example below that builds a key from a variable, rather than writing every element as a literal, uses it for exactly this.

<code>
// A Tuple literal can hold more than one element directly, the same as a
// Table or Lookup Table literal; binding it to a name goes through Tuple.
fullKey := Tuple [2007, "Boston"];

// AddTupleValue reaches a one-element Tuple the same way, then appends to
// it — the pattern used throughout this page, typically with a connected
// variable rather than a second literal like the one shown here.
yearOnly := Tuple [2007];
alsoFullKey := AddTupleValue yearOnly "Boston";
</code>

Every functor below that identifies a row or sub-table by key takes that key as a Tuple.

===== Retrieving values and rows =====

=== GetTableValue ===

Reads a single cell.

^ Parameter ^ Type ^ Required? ^ Description ^
| ''table'' | Table | Yes | The table to read from. |
| ''keys'' | Tuple | Yes | The key identifying the row. |
| ''column'' | name or index | Yes | Which value column to read. An index counts every column left to right, starting at 1, including key columns — so the first value column's index is one past the number of key columns, not 1 (see [[table_type|Table Type]]). |
| ''valueIfNotFound'' | matches the column | No | Returned instead of failing if the key isn't present. |

Output: the value at that key and column.

=== GetTableRow ===

Reads every value column for one key at once.

^ Parameter ^ Type ^ Required? ^ Description ^
| ''keys'' | Tuple | Yes | The key identifying the row. |
| ''table'' | Table | Yes | The table to read from. |

Output: the row's value columns, as a Tuple — not the key columns, since those are already known from the ''keys'' input.

=== GetLookupTableValue ===

The Lookup Table equivalent of ''GetTableValue'' — no column argument, since a Lookup Table only ever has one.

^ Parameter ^ Type ^ Required? ^ Description ^
| ''table'' | Lookup Table | Yes | The lookup table to read from. |
| ''key'' | Real | Yes | The key to look up. |
| ''valueIfNotFound'' | Real | No | Returned instead of failing if the key isn't present. |

Output: the value for that key.

=== GetTableColumn ===

Retrieves an entire column, across every row, as a new table containing just the keys and that one column — unlike the three functors above, which each read a single cell or a single row.

^ Parameter ^ Type ^ Required? ^ Description ^
| ''table'' | Table | Yes | The table to read from. |
| ''columnIndexOrName'' | name or index | Yes | Which value column to retrieve. |

Output: a table with the same keys as the input, holding only the one retrieved column.

**Example** — [[Calc Areas]] returns a single output of type Table, with columns ''Category'' (key), ''Area_In_Cells'', ''Area_In_Hectares'', and ''Area_In_Square_Meters'' — every area measure for every category, in one call. A common mistake is to treat this output as if it were a single hectares figure; it's a table, and the column of interest has to be read out of it:

<code>
areaTable := CalcAreas landscape .no;
// the Area_In_Hectares column
hectaresColumn := GetTableColumn areaTable 3;
</code>

**Example** — a small constant table and lookup table, each read with the functor above that matches its shape:

<code>
myLookupTable := LookupTable [
    "Key" "Value",
    1 10,
    2 20,
    3 30
];
// lookedUpValue will be 20.
lookedUpValue := GetLookupTableValue myLookupTable 2;

myTable := Table [
    "CityId*", "Population", "Area",
    1, 667137, 125,
    2, 39398, 6
];
// population will be 667137.
population := GetTableValue myTable [1] "Population";

// wholeRow will be the two-element Tuple (39398, 6) — Population then Area.
wholeRow := GetTableRow [2] myTable;
</code>

===== Iterating over Lookup Table values =====

A [[For Each]] loop run over a connected Lookup Table executes once per key. Inside the loop, a [[Step]] functor retrieves the current key — its input auto-binds to the container's current iteration value, the same mechanism described in [[ego_script#internal_output_ports|Internal output ports]], so it needs no explicit wiring. From there, the key can be used directly, or passed to [[Get Lookup Table Value]] to retrieve the corresponding value.

A loop like this can often run its iterations in parallel — see [[basic_data_flow|Basic Data Flow]] for the conditions that allow it.

> **Note:** Every loop in the examples below meets those conditions — no mux, nothing consumed outside the loop, no submodel output port — so the engine is free to run iterations in any order, or concurrently. The printed lines can come out in a different order than the source table, and if two iterations genuinely run at the same time, their output can even interleave rather than appearing as clean, separate lines. Forcing a specific order means forcing sequential execution, which means introducing a mux — a plain ''MuxValue 0 0'' is enough, at the cost of losing the parallelism these loops would otherwise qualify for; see [[basic_data_flow#the_one_exception_feedback_in_loops|The one exception: feedback in loops]].

**Example** — printing every entry of a Lookup Table. [[Print]] is itself a container — its ''initialMessage'' is printed before whatever it contains runs, so an empty ''<nowiki>{{ }}</nowiki>'' block is enough when the only goal is to print something. [[Create String]] builds a message from a format string and the values connected inside it, referenced the same way a Code expression references a hook (see [[ego_script#verbose_form|Verbose form]]) — by a numbered, type-prefixed tag (''<v1>'' for the first connected value, and so on). This example also uses ''{ initialMessage = message, logLevel = .result }'' [[ego_script#nominal_syntax|nominal syntax]] for ''Print'', and the ''_'' [[ego_script#positional_syntax|discard convention]] for outputs that aren't needed:

> **Note:** Dinamica prints every functor's own start and end at the Info level. Left unfiltered, a ''Print'' call's message would be buried in that noise. Wrapping the loop in [[Log Policy]], with ''maximumLogLevel = .result'', restricts what gets logged from inside it to Result and anything more severe — Unconditional, Error, Warning, Result; Info and beyond are suppressed, the same ordering documented on [[calculate_r_expression|Calculate R Expression]]. ''Print'''s own ''logLevel = .result'' then places the custom message at exactly that threshold, so it stays visible. Every example on this page uses both together for that reason.

<code>
myLookupTable := LookupTable [
    "Key" "Value",
    1 10,
    2 20,
    3 30
];

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach myLookupTable {{
        key := Step;
        value := GetLookupTableValue myLookupTable key;

        message := CreateString "(Key: <v1>, Value: <v2>)" {{
            NumberValue key 1;
            NumberValue value 2;
        }};

        _ := Print { initialMessage = message, logLevel = .result } {{ }};
    }};
}};
</code>

===== Iterating over Table rows =====

Table rows are iterated the same way, but need one extra functor: [[Get Table Keys]] returns a table mapping unique indices to the keys of the input table's first key column. When the key column is already Real-typed, those indices and the keys are the same numbers, so the mapping is trivial. When the key column is String-typed, the indices are distinct numeric placeholders, and a ''GetTableValue'' call inside the loop is needed to map the current index back to its actual String key — this indirection is //why// String-typed key columns are usually avoided when a table will be iterated (see [[table_type|Table Type]]).

A table with more than one key column can only be iterated one key column at a time this way. To examine every key column, nest loops — see [[#sub_tables|Sub-Tables]] below.

**Example** — printing every entry of a Table with a Real-typed key column. General tables are wrapped in ''Table'' rather than ''LookupTable'':

<code>
myTable := Table [
    "CityId*", "Population",
    1, 667137,
    2, 39398
];

allKeys := GetTableKeys myTable;

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach allKeys {{
        key := Step;
        value := GetTableValue myTable key "Population";

        message := CreateString "(Key: <v1>, Value: <v2>)" {{
            NumberValue key 1;
            NumberValue value 2;
        }};

        _ := Print { initialMessage = message, logLevel = .result } {{ }};
    }};
}};
</code>

**Example** — the same, but with a String-typed key column. This is the extra-indirection case described above: ''GetTableKeys'' returns indices, not the original keys, so an extra ''GetTableValue'' inside the loop maps each index back to its String key before it can be used:

<code>
myStringKeyedTable := Table [
    "CityName*#string", "Population",
    "Boston", 667137,
    "Chelsea", 39398
];

indexToKey := GetTableKeys myStringKeyedTable;

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach indexToKey {{
        index := Step;
        key := GetTableValue indexToKey index 2;
        value := GetTableValue myStringKeyedTable key "Population";

        message := CreateString "(Key: <s1>, Value: <v1>)" {{
            NumberString key 1;
            NumberValue value 1;
        }};

        _ := Print { initialMessage = message, logLevel = .result } {{ }};
    }};
}};
</code>

**Example** — a table with more than one value column. Nothing new is needed: call ''GetTableValue'' once per value column and combine the results:

<code>
myTable := Table [
    "CityId*", "Population", "Area",
    1, 667137, 125,
    2, 39398, 6
];

allKeys := GetTableKeys myTable;

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach allKeys {{
        key := Step;
        population := GetTableValue myTable key "Population";
        area := GetTableValue myTable key "Area";

        message := CreateString "(CityId: <v1>, Population: <v2>, Area: <v3>)" {{
            NumberValue key 1;
            NumberValue population 2;
            NumberValue area 3;
        }};

        _ := Print { initialMessage = message, logLevel = .result } {{ }};
    }};
}};
</code>

===== Sub-Tables =====

A **sub-table** is a table containing only the rows matching one specific value of a key, with that key column removed from the result. Sub-tables are how Dinamica handles tables with more than one key column: peel off one key at a time, working with what remains.

=== GetTableFromKey ===

Retrieves the rows matching the given key(s), with those key columns dropped from the result.

^ Parameter ^ Type ^ Required? ^ Description ^
| ''table'' | Table | Yes | The table to read from. |
| ''keys'' | Tuple | Yes | The leftmost key(s) identifying the sub-table. |

Output: the matching sub-table.

=== SetTableByKey ===

Inserts or replaces the rows matching the given key(s).

^ Parameter ^ Type ^ Required? ^ Description ^
| ''table'' | Table | Yes | The table to update. |
| ''keys'' | Tuple | Yes | The leftmost key(s) identifying where the sub-table goes. |
| ''subTable'' | Table | Yes | The replacement rows. Column names and types must match ''table''. |

Output: the updated table.

Both [[Get Table From Key]] and [[Set Table By Key]] only operate on the table's **leftmost** key column(s) — they can't reach into the middle of a composite key. If the key you need isn't leftmost, reorder the columns first with [[Reorder Table Column]], which moves one column (key or value) to a new index; key and value columns can't be moved across each other.

**Worked example.** Take a table of commodity prices, keyed by ''Year'' and ''City'':

^ Year* ^ City* ^ Price ^
| 2004 | Boston | 1200 |
| 2004 | Chelsea | 1453 |
| 2007 | Boston | 4332 |
| 2007 | Chelsea | 233 |

Retrieving the sub-table for ''Year'' 2007 — a single key, since ''Year'' is leftmost — leaves ''City''/''Price'' pairs:

^ City* ^ Price ^
| Boston | 4332 |
| Chelsea | 233 |

To instead retrieve by ''City'' first, ''Year'' would need to be reordered ahead of it, since ''GetTableFromKey'' can only key off the leftmost column(s). With three key columns, the same idea nests: peel off the leftmost key to get a sub-table, then peel off the next leftmost key of //that// sub-table, and so on — which is exactly how nested ''ForEach'' loops iterate a multi-key table one column at a time; see [[ego_script#container_functors|Container functors]] for how nesting containers this way affects execution order.

**Example** — building the table above, retrieving the 2007 sub-table, updating one of its prices with [[Set Table Cell Value]], writing it back, and finally reordering ''City'' ahead of ''Year'' to key by ''City'' instead:

<code>
priceTable := Table [
    "Year*", "City*", "Price",
    2004, "Boston", 1200,
    2004, "Chelsea", 1453,
    2007, "Boston", 4332,
    2007, "Chelsea", 233
];

// Retrieve the City*/Price sub-table for Year 2007.
pricesIn2007 := GetTableFromKey priceTable [2007];

// Replace the price for Chelsea within that sub-table.
updatedPricesIn2007 := SetTableCellValue pricesIn2007 "Price" ["Chelsea"] 999;

// Write the modified sub-table back under the same key.
priceTable2 := SetTableByKey priceTable [2007] updatedPricesIn2007;

// Move City (currently index 2) ahead of Year (index 1), so sub-tables can
// instead be retrieved by City first.
priceTableByCity := ReorderTableColumn priceTable2 "City" 1;
// citySubTable will be the Year*/Price pairs for Boston.
citySubTable := GetTableFromKey priceTableByCity ["Boston"];
</code>

**Example** — printing every entry of a table with more than one key column. [[Get Table Keys]] only ever iterates the //first// key column, so a table with several needs one nested loop per extra key: peel off a sub-table for each ''Year'', then iterate the ''City'' key within it:

<code>
priceTable := Table [
    "Year*", "City*", "Price",
    2004, "Boston", 1200,
    2004, "Chelsea", 1453,
    2007, "Boston", 4332,
    2007, "Chelsea", 233
];

yearKeys := GetTableKeys priceTable;

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach yearKeys {{
        year := Step;
        pricesInYear := GetTableFromKey priceTable year;

        cityKeys := GetTableKeys pricesInYear;

        _ := ForEach cityKeys {{
            cityIndex := Step;
            // City is String-typed, so cityIndex is a placeholder, not the real
            // city — map it back the same way as any String-typed key column.
            city := GetTableValue cityKeys cityIndex 2;
            price := GetTableValue pricesInYear city "Price";

            message := CreateString "(Year: <v1>, City: <s1>, Price: <v2>)" {{
                NumberValue year 1;
                NumberString city 1;
                NumberValue price 2;
            }};

            _ := Print { initialMessage = message, logLevel = .result } {{ }};
        }};
    }};
}};
</code>

A modified version of the same example: instead of reading ''Price'' from the City-only sub-table, build the full composite key — both ''Year'' and ''City'' together, as a Tuple — and read directly from the original table:

<code>
priceTable := Table [
    "Year*", "City*", "Price",
    2004, "Boston", 1200,
    2004, "Chelsea", 1453,
    2007, "Boston", 4332,
    2007, "Chelsea", 233
];

yearKeys := GetTableKeys priceTable;

LogPolicy { maximumLogLevel = .result } {{
    _ := ForEach yearKeys {{
        year := Step;
        pricesInYear := GetTableFromKey priceTable year;

        cityKeys := GetTableKeys pricesInYear;

        _ := ForEach cityKeys {{
            cityIndex := Step;
            city := GetTableValue cityKeys cityIndex 2;

            // The composite key, built by appending City onto Year.
            fullKey := AddTupleValue year city;

            // Reads every value column for that key from the original table, as a
            // Tuple — here just Price, since Price is the table's only value column.
            row := GetTableRow fullKey priceTable;

            price := GetTableValue priceTable fullKey "Price";

            message := CreateString "(Year: <v1>, City: <s1>, Price: <v2>)" {{
                NumberValue year 1;
                NumberString city 1;
                NumberValue price 2;
            }};

            _ := Print { initialMessage = message, logLevel = .result } {{ }};
        }};
    }};
}};
</code>

''GetTableFromKey'' is still used to enumerate which ''City'' keys exist for a given ''Year'' — nothing else on this page discovers a table's keys without it. What changes is the read itself: ''GetTableRow'' and ''GetTableValue'' are both called against ''priceTable'' with the full ''(Year, City)'' Tuple, not against ''pricesInYear'' with ''City'' alone.

===== Storing results across a loop =====

A [[Mux Table]] or [[Mux Lookup Table]] can carry a Table or Lookup Table across the iterations of a loop, the same way [[Mux Value]] carries a single value — see [[ego_script#carrying_and_selecting_values_across_iterations|Carrying and selecting values across iterations]]. Each iteration reads the mux's current output, adds a row to it with ''AddTableRow'', and feeds the result back in as the mux's ''feedback'' input for the next iteration, building up a result table one row at a time.

The accumulated table is read after the loop the same way any container's internal result is read from outside it: a functor after the loop simply takes the accumulator's feedback variable as an input. There's nothing special about this — the loop is guaranteed to finish before anything depending on it runs, per [[basic_data_flow|Basic Data Flow]].

<code>
sourceLookup := LookupTable [
    "Key" "Value",
    1 667137,
    2 39398,
    3 181045
];

emptyResults := Table [
    "CityId*#real", "DoubledPopulation#real"
];

_ := ForEach sourceLookup {{
    accumulated := MuxTable emptyResults nextAccumulated;

    key := Step;
    population := GetLookupTableValue sourceLookup key;
    doubled := $ [ $population * 2 ];

    newRow := AddTupleValue key doubled;
    nextAccumulated := AddTableRow accumulated newRow;
}};

// finalResults holds every row added across all iterations. Use nextAccumulated,
// not accumulated (the mux's own output), which lags one iteration behind.
// The Table carrier passes it through, since := always binds a functor call.
finalResults := Table nextAccumulated;
</code>

> **Note:** This is also a reason to avoid reading ''accumulated'' anywhere else in the same iteration, beyond its use in ''AddTableRow''. The engine schedules operations to minimize copying: when a value only needs to be read before it's destructively updated, the reads are scheduled first and the update last, avoiding a copy entirely. But if two or more functors both need to destructively update the same value, no scheduling avoids a copy for all of them — only one destructive update can safely be the last one to run. See [[basic_data_flow#destructive_updates|Basic Data Flow]] for this same rule stated generally, beyond just tables.

**Why this isn't the best approach.** The mux above disqualifies this loop from the parallel execution described earlier — it's what ties every iteration to the one before it, forcing the loop to run sequentially even though the per-iteration work is otherwise completely independent (each row's value depends only on that row's own key).

When the transformation is this simple — one output row per input row, computed independently — the calculator shorthand already covered in [[calculate_functors#5_lookup_table_operators|Lookup Table Operators]] produces the same result without a loop, a mux, or the sequential cost that comes with one:

<code>
doubledResults := % [ %sourceLookup[line] * 2 ] "CityId" "DoubledPopulation" sourceLookup;
</code>

===== Choosing between Tables and Lookup Tables =====

A Lookup Table's fixed shape — one Real key, one Real value — makes it faster to query and iterate than a general Table, and it supports proximity-based lookups (nearest key, linear interpolation) that Tables don't. Prefer a Lookup Table whenever the data actually fits that shape, including inside map/value expressions, where Lookup Table operands add less evaluation overhead than Table operands — see [[calculate_functors#5_lookup_table_operators|Lookup Table Operators]] and [[calculate_functors#6_multi-column_table_operators|Multi-Column Table Operators]] for how each is queried from within an expression.

Reach for a general Table instead when a single value per key isn't enough — when a row needs more than one associated value, or a String value, or when rows need to be indexed by more than one key at once.