Function Names
table expand, table expand ignore caseDescription
These functions work very similarly like table integrate(). Instead of stopping at the first match in the lookup table, this function family looks up all entries in the lookup table. The search procedure through the lookup table will always be top-down. If only one match has been identified, then the behavior is the same as in table integrate(). In case 2 or more matching enties are found in the lookup table, then the following happens:
- In the target table, additional rows will be inserted below the current row being procesed. The rows further below will shift downward accordingly.
- All contents in the original row will be copied into these new rows.
- Then the looked up information will be integrated into the corresponding target rows, using the same principle as in the function table integrate() using the operation defined by the table integration operation identifiers.
- Some operation identifiers make use of special rules for dealing with blank contents in the lookup table.
- In case there is no match for a partiuclar row in the target table, then the row stays unchanged (meaning that these rows will not be removed).
Additional features:
- An expression to select rows (8th function parameter) can be specified to limit the lookup process to selected rows.
- An additional column (9th function parameter) can be specified in the lookup table to count the number of matches.
Call as: procedure or function
Restrictions
Indirect parameter passing is disabled
Parameter count
6 - 9 (For parameter description and return values: see table integrate() )
Examples
table load( lookup, "Examples/Table Lookup Example 1.csv" );
table initialize( target, { { Name, Favorite Country },
{ Antonio, Italy}, { Fritz, Germany }, { Andreas, Germany }, { Pascale, France}, { Mike, USA } } );
echo("Target table before looking up with 'table digest':");
table list ( target );
echo("Lookup table:");
table list ( lookup );
count[] = table expand ( target, Favorite Country, {Meal, Rating Sum},
lookup, Country, { Recommendation, Rating }, { overwrite, add }, true, {Counter,Counter} );
echo("Target table after looking up with 'table digest':");
table list ( target );
echo("Lookup table after looking up to check the 'Counter' column:");
table list ( lookup );
echo("Number of accesses: ", count[] );
Output
Target table before looking up with 'table digest':
0 : Name | Favorite Country
1 : Antonio | Italy
2 : Fritz | Germany
3 : Andreas | Germany
4 : Pascale | France
5 : Mike | USA
Lookup table:
0 : City | Country | Meal or Drink | Recommendation | Rating
1 : Hamburg | Germany | Meal | Hering | 2
2 : Hamburg | Germany | Meal | Seafood | 3
3 : New York | USA | Meal | Pizza | 5
4 : New York | USA | Meal | Hamburger | 5
5 : Florence | Italy | Drink | Chianti | 4
6 : Florence | Italy | Meal | Tagliatelli | 5
7 : Edinburgh | G. Britain | Drink | Single Malt | 3
8 : Edinburgh | G. Britain | Meal | Fish & Chips | 3
9 : New York | USA | Drink | Lager | 3
10 : Camarque | France | Meal | Cheese | 3
11 : Camarque | France | Spice | Fleur du sel | 4
12 : Boston | USA | Meal | Lobster | 4
13 : Plzen | Czech Rep. | Drink | Pils | 4
14 : Munich | Germany | Meal | Pork | 3
15 : Munich | Germany | Drink | Weissbier | 5
Target table after looking up with 'table digest':
0 : Name | Favorite Country | Meal | Rating Sum | Counter
1 : Antonio | Italy | Chianti | 4 | 1
2 : Antonio | Italy | Tagliatelli | 5 | 1
3 : Fritz | Germany | Hering | 2 | 1
4 : Fritz | Germany | Seafood | 3 | 1
5 : Fritz | Germany | Pork | 3 | 1
6 : Fritz | Germany | Weissbier | 5 | 1
7 : Andreas | Germany | Hering | 2 | 1
8 : Andreas | Germany | Seafood | 3 | 1
9 : Andreas | Germany | Pork | 3 | 1
10 : Andreas | Germany | Weissbier | 5 | 1
11 : Pascale | France | Cheese | 3 | 1
12 : Pascale | France | Fleur du sel | 4 | 1
13 : Mike | USA | Pizza | 5 | 1
14 : Mike | USA | Hamburger | 5 | 1
15 : Mike | USA | Lager | 3 | 1
16 : Mike | USA | Lobster | 4 | 1
Lookup table after looking up to check the 'Counter' column:
0 : City | Country | Meal or Drink | Recommendation | Rating | Counter
1 : Hamburg | Germany | Meal | Hering | 2 | 2
2 : Hamburg | Germany | Meal | Seafood | 3 | 2
3 : New York | USA | Meal | Pizza | 5 | 1
4 : New York | USA | Meal | Hamburger | 5 | 1
5 : Florence | Italy | Drink | Chianti | 4 | 1
6 : Florence | Italy | Meal | Tagliatelli | 5 | 1
7 : Edinburgh | G. Britain | Drink | Single Malt | 3 |
8 : Edinburgh | G. Britain | Meal | Fish & Chips | 3 |
9 : New York | USA | Drink | Lager | 3 | 1
10 : Camarque | France | Meal | Cheese | 3 | 1
11 : Camarque | France | Spice | Fleur du sel | 4 | 1
12 : Boston | USA | Meal | Lobster | 4 | 1
13 : Plzen | Czech Rep. | Drink | Pils | 4 |
14 : Munich | Germany | Meal | Pork | 3 | 2
15 : Munich | Germany | Drink | Weissbier | 5 | 2
Number of accesses: 32
See also
table expand fast
table expand smart
table expand with rules
table expand with rules once
table lookup
table integrate
table digest