Introduction
The following table lists all valid operation identifiers which can be used in the 7th function parameter of the
table integrate() and table expand() function families.
The operation identifier rules how the value or data retrieved from the output column in the lookup table shall be combined
with the existing data in the destination column in the target table.
| Operation identifier | Description |
|---|---|
| overwrite | Output data overwrites destination data (same behavior like in the table lookup() function family) |
| none | Output data does not overwrite destination data (it may be meaningless, but useful if the operation identifiers provided in variables) |
| overwrite if blank | Output data overwrites destination data if existing destination data is blank |
| overwrite except blank | Output data overwrites destination data if output data is not blank |
| append | Output data is appended to the destination data |
| append with space | Output data is appended to the destination data with one space character ' ' inbetween |
| append with comma | Output data is appended to the destination data with a comma inbetween. No additional space added behind the comma. |
| min abc | Chooses the first one by alphabetic sorting order. Note: blank string has highest order. |
| min abc ignore blanks | Chooses the first one by alphabetic sorting order. Blank values are not preferenced. |
| max abc | Chooses the last one by alphabetic sorting order. |
| min | Chooses the smaller (numeric) value. Blanks and non-numeric data are treated as 0 |
| min ignore blanks | Chooses the smaller (numeric) value. Non-numeric data are treated as 0. Blanks are not chosen. |
| min ignore zero | Chooses the smaller (numeric) value. Both non-numeric data and 0 are not chosen. |
| max | Chooses the bigger (numeric) value. Blanks and non-numerals are treated as 0 |
| max ignore blanks | Chooses the bigger (numeric) value. Non-numeric data are treated as 0. Blanks are not chosen. |
| max ignore zero | Chooses the bigger (numeric) value. Both non-numeric data and 0 are not chosen. |
| add | Output value is added to destination value. Blanks and non-numerals are treated as 0 |
| sub | Output value is subtracted from destination value. Blanks and non-numerals are treated as 0 |
| mul | Destination value is multiplicated with the output value. Non-numeral entries are treated as 0. Attention: See 1. |
| mul ignore blanks | Destination value is multiplicated with the output value if not blank. Non-numeric ata are treated as 0. |
| mul ignore zero | Destination value is multiplicated with the output value as long the output data is neither blank nor 0. |
| div | Destination value is divided by the output value. Result will be 0 if entry is 0 or a non-numeral. Attention: See 1. |
| inc | Destination value is incremented by specified value in lookup table. In the next entry, this next higher entry is used for further increase. Negative values decrease accordingly. Attention: See 2. |
| dec | Destination value is decremented by specified value in lookup table. In the next entry, this next higher entry is used for further increase. Negative values increase accordingly. Attention: See 2. |
| average | For functions table lookup..., table integrate...... and table expand...: Calculates the average of 2 values: The one in the target table (blanks and non-numerals = 0) and in the lookup table. Note: For functions table digest...: The average of all values from the lookup table will be calculated and written into the target table (sum of them divided by number of row found). Highly advisable to keep the field in the target table at 0 or blank in order to avoid having the average values being added up here with the old vale in the target table. |
| index | Inserts the index into destination value, i.e. the current count of inserted rows. The index starts with 0. For example, if 3 matches are found in the lookup table, then the index for the 3 rows (2 of them are new) will be 0, 1 and 2. The related value from the lookup table is ignored here, but you must specify one in order to match the column count. |
1 Applicable to table expand() function family: The identifiers mul and div apply a special algorithm: The residual value, which is the value in the destination table before the call, is subtracted by the the calculated (i.e. multiplied resp. divided) values in the previous matches. Very useful if the lookup table contains a few items with percentages (values 0..1) related to ingredients and the target table contains the total weight of the food. 2 Applicable to table expand() function family: The identifiers inc and dec apply a special algorithm: Blank entries in look-up table retrieves the residual value (original value in target table added (for inc) or subtracted (for dec) by all previous calculations belonging together).