Overview
In many application cases, information from multiple sources need to be merged and overlapping information consolidated based on rules provided. Data management associated with merging or rearanging mulitple organizations are typical examples.
You can use the Lookup Function Family to merge data
| table lookup() | Look up for data from other tables. |
| table lookup ignore case() | Also useful, as lookup comparisons ignore cases. |
| table lookup smart() | In this case, the lookup table allows wildcards, commas for multiple choices, etc. |
| table lookup smart ignore case() | Also useful, as lookup comparisons ignore cases. |
| table integrate() | Works similar like table lookup(), but applies rules to combine existing data with data looked up, e.g. adding them up. |
| table expand() | Works similar like table lookup(), but in case of multiple matches, additional rows will be inserted into the destination table automatically. |
Merge multiple tables
| table merge() | Merge one source table into another existing destination table. |
| table merge extend columns() | Same as above, where the destination table may be extended with further columns found in the source table. |
| table overlay() | Merge contents of one table into the other table, but only if no additional rows need to be created. |
| table subtract() | Use one table to subtract, i.e. remove rows, in the other table. E.g. remove particular people in table A from table B. |
| table intersect() | Obtain the intersection of two tables, e.g. with full names provided in both tables. |
Wikipedia Example (continued from step 3)
Do some validation on this table:
- Merge country table c2 into c1, with the additional columns with population counts from a different source.
- Remove questionable countries which are not globally recongnized.
Simple Example
table merge extend columns ( c2, c1, Country );
table delete selected rows ( c1, [Continent]='' ); // Questionable countries
echo( "Table C1: ");
table list ( c1, briefly, 4, last col, 2 ); // List just 3 columns and first and last 4 rows
Merging has been done.
Table C1:
0 : Country | Area | Inhabitants
1 : Afghanistan | 652230 | 41100000
2 : Egypt | 1001450 | 103500000
3 : Albania | 28748 | 2800000
4 : Algeria | 2381741 | 44900000
... :
194 : Central African Republic | 622984 | 5600000
195 : Cyprus | 9251 | 1300000
196 : China | |
197 : Denmark | |