B4P Reference

Function Names

table integrate, table integrate ignore case, table integrate top down, table integrate top down ignore case

Description

These functions work very similarly like the corresponding table lookup() function, with the exception that the data retrieved from the lookup table will not be simply written back to the target table, but an operation is performed with the data already existing in the target table with the retrieved data and then written back. Example: You look up data for some numbers and want to add them to the already existing values in the target table. If the suffix ignore case is added to the function name, then all comparisons made are case insensitive.

Function 'table integrate'

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 integrate top down ... instead. The output is the same in both cases.

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
This function provides a table context for partial table specifications with table name and row number for selected function parameters

Parameter count

6 - 9

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. Example: { Family Name, First Name } when looking up a phone directory table. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: At least one header name or column number must be specified The number of input columns in the target table must equal to the number of 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. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: Any number of header names or column numbers may be specified (0 columns means pick up nothing, in case you need to count the lookups only) The number of destination columns in the target table must equal to the numer of output columns in the lookup table Missing columns will be created.
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. Example: { Last Name, Given Name } when looking up a phone directory table. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: At least one header name or column number must be specified The number of search columns in the loookup table must equal to the number of input 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. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: Any number of header names or column numbers may be specified (0 columns means pick up nothing, in case you need to count the lookups only) The number of output columns in the lookup table must equal to the numer of destination columns in the target table Exception: Specify '#row' to retrieve the row number from the lookup table. Note: "#row" in double quotation marks (quoted string) is excluded from this exception and treated as a header name.
Opt. 7. input	set or string	Operation Identifier Specify one of the table integration operation identifiers The number of operation identifiers is less than the number of columns specified in the 3rd (output columns) and 6th parameter (destination columns), then the default value 'overwrite' is assumed for the remaining columns. If this parameter contains more elements than columns retrieved, then the exess ones will be ignored. Use a string to specify one value Use set to specify multiple values No values can be provided by using an empty set {} Default value: {overwrite, ...} (overwrite applies to all parameters)
Opt. 8. code	expression :string	Expression to select rows Same as 7th function parameter in table lookup(). This function parameter allows you to select the rows in the target table in order to limit the lookups to those rows only. The table context for partial table specifications is available for referencing just the columns easily. Example: [Year]>=2022. Attention: Comparison operators = and <> (instead of == and !=) may hijack the next function parameters for its own use as additional comparison operands, e.g. a[]=1,3,5. As long this comparison is not made in the last function parameter, then put parentheses around them, e.g. ... , (a[]=1,3,5), .... Default value: true (all rows selected)
Opt. 9. input	table column	Counter column Same as 8th function parameter in table lookup(). 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. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: At most one header name or column number must be specified If the column is missing in the lookup table, then it will be created. Hint: If you need this function parameter, but not the 8th function parameter, then write true into the 8th parameter. Default value: {} (no column selected)

Return value

Type	Description
numeral	Number of lookups made Every successful lookup (input columns match with search columns are matching) will count 1.

Examples

       table initialize( lookup,

       {  {  Names, Sports,      Scores },

          {  Abel,  tennis,      10 },          {  Bea,   squash, 14 },

          {  Chuck, racquetball, 13 },          {  Dan,   tennis, 8 },

          {  Eva,   squash,       9 },          {  Finn,  tennis, 12 },

          {  Gini,  racquetball, 12 },          {  Hugo,  squash, 11 } } );



       table initialize( target, {{ Sport, Name, Score 1, Score 2, Score 3, Score 4 },

               { racquetball, "", 5, 100, 12  },

               { squash,      "", 5, 100, 12  },

               { tennis,      "", 5, 300, 10  },

               { racquetball, "", 1, 100, 55  },

               { tennis,      "", 5, 100, 12,8  } } );



       // Picks the 1st match and integrates the results

       count[] = table integrate( target, Sport,  { Name,  Score 1 .. Score 4 },

                                        lookup, Sports, { Names, 4:Scores }, { overwrite, add, sub, max, average }, true, Counter );



       echo("Lookup table polled:");

       table list( lookup );



       echo("Target table after lookup (", count[], " items found):");

       table list ( target );

Output

Lookup table polled:
    0 : Names | Sports      | Scores | Counter
    1 : Abel  | tennis      | 10     | 2      
    2 : Bea   | squash      | 14     | 1      
    3 : Chuck | racquetball | 13     | 2      
    4 : Dan   | tennis      | 8      |        
    5 : Eva   | squash      | 9      |        
    6 : Finn  | tennis      | 12     |        
    7 : Gini  | racquetball | 12     |        
    8 : Hugo  | squash      | 11     |        

Target table after lookup (5 items found):
    0 : Sport       | Name  | Score 1 | Score 2 | Score 3 | Score 4
    1 : racquetball | Chuck | 18      | 87      | 13      | 6.5    
    2 : squash      | Bea   | 19      | 86      | 14      | 7      
    3 : tennis      | Abel  | 15      | 290     | 10      | 5      
    4 : racquetball | Chuck | 14      | 87      | 55      | 6.5    
    5 : tennis      | Abel  | 15      | 90      | 12      | 9

Try it yourself: Open LIB_Function_table_integrate.b4p in B4P_Examples.zip. Decompress before use.

table integrate ...