Function Names
table digest, table digest 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 and includes these rows looked up in the data integration proedure. 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:
- The integration procedure will be applied on the same target table row and destination columns using the operation defined by the table integration operation identifiers. Example: Using the operation identifier add: All numeric values found in the lookup table will be added to the (already existing) value in the corresponding destination column.
- In case of no matches, the row stays unchanged, i.e. the row in the target table will not be deleted.
- For the operation identifier average, the collected values plus the already existing value in the target table will be added to gether and divided by the number of rows found in the lookup table. This means that it is advisalbe to keep the initial value in the destination column blank or zero in order to calculate the average values proper.y.
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 digest ( target, Favorite Country, {Meal, Rating Sum},
lookup, Country, { Recommendation, Rating }, { append with space, 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 Tagliatelli | 9 | 2
2 : Fritz | Germany | Hering Seafood Pork Weissbier | 13 | 4
3 : Andreas | Germany | Hering Seafood Pork Weissbier | 13 | 4
4 : Pascale | France | Cheese Fleur du sel | 7 | 2
5 : Mike | USA | Pizza Hamburger Lager Lobster | 17 | 4
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 digest smart
table digest with rules
table digest with rules once
table lookup
table integrate
table expand