Function Names
table lookup, table lookup ignore case, table lookup top down, table lookup top down ignore caseDescription
This function processes the specified target table (1st function parameter) row-by-row by looking up data from a lookup table (4th function parameter). The lookup procedure takes place as follows:
- The data specified in one or more input columns will be compared with corresponding search columns (5th function parameter) in the lookup table.
- When a matching row in the lookup table has been identified (ie. contents in the input and search columns match), then the contents specified in one or more output columns (6th function parameter) in the lookup table will be retrieved and written into the destination columns (3rd function parameter) in the target table.
- These two steps will be repeated for every row in the target table.
If the suffix ignore case is added to the function name, then all comparisons made are case insensitive.
The lookup function contains powerful comparison algorithms which will index the lookup table and accelerate the lookup performance significantly
if it exceeds a size where the time overhead of creating an internal indexation starts paying off. This scheme accelerates the lookup procedure
to a search time ratio versus table size n to O ( log(n) ).
For example, looking up a table containing ca 64,000 entries requires 16 comparisons only. For small tables, indexing will be skipped in order to avoid the
initial overhead to create an indexation and the lookup takes place in a simple top-down approach beginning with row 1.
If a large lookup table is considered, but you know that most items looked up lie in to rows (say: roughly first 30 rows), then you may
experience a performance improvement using the function name table lookup top down ... instead. The output is the same in both cases.
Additional features:
- An expression to select rows (7th function parameter) can be specified to limit the lookup process to selected rows.
- An additional column (8th 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
This function provides a table context for partial table specifications with table name and row number for selected function parameters
Parameter count
6 - 8
Parameters
| No. | Type | Description |
|---|---|---|
| 1. input |
string | Name of target table The table referred here is assumed to be the more recent table. |
| 2. input |
table columns | Input columns Specify 1 or more columns in the input table which contains the information to compare with the search columns in the lookup table.
|
| 3. input |
table columns | Destination columns Specify columns in the input table where to copy the information retrieved from output columns in the lookup table.
The column header names do not need to be the same the corresponding headers in the lookup table.
Example: { Phone Number, Fax Number } when looking up a phone directory table.
|
| 4. input |
string | Name of lookup table The table referred here is assumed to be the more recent table. |
| 5. input |
table columns | Search columns Specify 1 or more columns in the lookup table which contains the information to compare with the corresponding inputs columns in the target table.
|
| 6. input |
table columns | Output columns Specify columns in the lookup table for the information to be retrieved and copied to the destination columns in the target table.
The column header names do not need to be the same the corresponding headers in the lookup table.
Example: { Phone Number, Fax Number } when looking up a phone directory table.
|
| Opt. 7. code |
expression :string |
Expression to select rows This function parameter allows you to select the rows in the target table in order to limit the lookups to those rows only.
|
| Opt. 8. input |
table column | Counter column Specify 1 column in the lookup table which shall be used to count the successful lookup made. This function is useful in order to
get an idea of the data in the lookup table searched frequently, rarely or not at all. If the column already contains numbers, then
the existing numbers will be incremented, i.e. no reset to zero.
Hint: If you need this function parameter, but not the 7th function parameter, then write true into the 7th parameter. Default value: {} (no column selected) |
Return value
| Type | Description |
|---|---|
| numeral | Number of lookups made Every row successfully matched in the lookup table counts 1. |
Examples
table load( lookup, "Examples/Table Lookup Example 1.csv" );
table initialize( target, { Town, Florence, Venice, Florence, Edinburgh, New York, Hamburg, Florence, Munich, New York } );
table copy table( target, t1 );
count[] = table lookup ( t1 , Town, { Food or Beverage, Recommended, Row Number },
lookup, City, { Meal or Drink, Recommendation, '#row' }, true, Counter );
echo("Lookup table after 1st lookup, note the 'Counter' column added:");
table list( lookup );
echo("Target table after lookup (", count[], " items found):");
table list( t1 );
count[] = table lookup ( target , Town, { Food or Beverage, Recommended, Row Number },
lookup, City, { Meal or Drink, Recommendation, '#row' }, ([Town]=Florence,Venice), Counter );
echo("Target table after lookup (Florence and Venice only) (", count[], " items found):");
table list( target );Output
Lookup table after 1st lookup, note the 'Counter' column added:
0 : City | Country | Meal or Drink | Recommendation | Rating | Counter
1 : Hamburg | Germany | Meal | Hering | 2 | 1
2 : Hamburg | Germany | Meal | Seafood | 3 |
3 : New York | USA | Meal | Pizza | 5 | 2
4 : New York | USA | Meal | Hamburger | 5 |
5 : Florence | Italy | Drink | Chianti | 4 | 3
6 : Florence | Italy | Meal | Tagliatelli | 5 |
7 : Edinburgh | G. Britain | Drink | Single Malt | 3 | 1
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 | 1
15 : Munich | Germany | Drink | Weissbier | 5 |
Target table after lookup (8 items found):
0 : Town | Food or Beverage | Recommended | Row Number
1 : Florence | Drink | Chianti | 5
2 : Venice | | |
3 : Florence | Drink | Chianti | 5
4 : Edinburgh | Drink | Single Malt | 7
5 : New York | Meal | Pizza | 3
6 : Hamburg | Meal | Hering | 1
7 : Florence | Drink | Chianti | 5
8 : Munich | Meal | Pork | 14
9 : New York | Meal | Pizza | 3
Target table after lookup (Florence and Venice only) (3 items found):
0 : Town | Food or Beverage | Recommended | Row Number
1 : Florence | Drink | Chianti | 5
2 : Venice | | |
3 : Florence | Drink | Chianti | 5
4 : Edinburgh | | |
5 : New York | | |
6 : Hamburg | | |
7 : Florence | Drink | Chianti | 5
8 : Munich | | |
9 : New York | | |
See also
table lookup fast
table lookup once
table lookup smart
table lookup smart once
table lookup with rules
table integrate
table expand
table digest