B4P Reference

Function Names

table compare, table compare ignore case, table compare ignore blanks, table compare ignore both

Description

This function compares two tables intelligently and generates a comparison report with a level of detail you can define. The comparison can either take place using one or more columns for orientation (e.g. last name and first name) and compare the other values accordingly, or do a fully automatic comparison without orientation. Comparison includes checking all changes made, rows added and/or removed.

The function endings ...ignore case/blanks/both make comparisons tolerant to upper/lower case and/or spaces between words.

This function is able to deal with tables where the columns and data rows of the two tables are different.

The report contains the the comparison results. See Table Comparison Reports for details.

Call as: procedure or function

Restrictions

Indirect parameter passing is disabled

Parameter count

4 - 7

Parameters

No.

Type

Description

1.
input

string

Name of existing newer table

The table referred here is assumed to be the more recent table.

2.
input

string

Name of existing older table

The table referred here is assumed to be the baseline table.

3.
input

string

Name of report table

A new table will created. If the table already exists, then it will be initialized.

Opt. 4.
input

table columns

Orientation identifier columns

If the table contains unique identifiers or other columns suitable for identifying the entry and providing orientation, for example a combination of last and first name, a social security number, a product serial number, company name, etc., then specify them in this function parameter. It does not matter if some non-unique orientation values (e.g. 2 persons sharing the same first and last names, or only first names are known) do exist. Overall, use of orientation columns will accelerate the comparison process significantly. A change of a value in an orientation column is interpreted as a new row added (and a different row possibly deleted).

The same orientation headers or column numbers must exist in both tables. Otherwise, exceptions will be asserted.

See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function:

Any number of header names and column numbers may be specified

Default value: {} (none)

Opt. 5
input

string

Reporting option

Choose one of the following values:

entire table	The report has full table size: It contains all rows and all columns found in both tables, including the equal ones, and the differences are noted in their respective locations.
entire rows	The report contains all columns found in both tables, but lists only the rows where the contents differ.
differences	Shows changes, otherwise blank fields
different columns	Like above, but deletes all columns where no different data have been found in the rows below below.
different rows	Like "differences", but deletes all rows where no differences have been encountered.
condensed	Combination of 'different columns' and 'different rows': Only the rows as well as columns where differences are identified will be shown.
journal	Sequential change report: 1 difference documented per row with their locations in the old and new table as well as the old and new contents.

Default value: differences

Opt. 6.
input

string

Change indication call-back function name

If needed, specify the name of a user-defined function which shall be called every time a difference has been identified. In the function, you can generate and return a custom-formulated way how to describe the difference. This function is supported in conjunction with all reporting options except journal.

Attention: This function is not called for contents vanished by removal of columns. Details are described below.

If no function is provided, following rule applies: Show new contents if available (if reporting options is entire table or entire rows), or different. Show old contents in brackets [ ] if removed.

Default value: '' (blank / no function provided)

Opt. 7.
input

boolean

Call function only if different

If true: Call change indication function only if the contents are different.
If false: Call change indication function for every cell

Default value: false

Return value

Type

Description

numeral

Number of tables created or initialized

Returns one of the following values:

identical	Both tables are perfectly identical, including same order of rows and columns.
equal	Both tables contain same contents, however the rows and/or columns may be in a different order (e.g. sorted and unsorted table compared)
less	The newer table contains fewer rows and/or columns, but the remaining data is equal
more	The newer table contains additional rows and/or columns, but the existing data is equal
different	The newer table is different (contents have changed or both rows have been added and removed)
failed	An error has occurred. No comparison made.

Examples

       table load( t1, "Examples/Cities.csv" );

       table keep columns ( t1, {City, Country, Inhabitants, Famous attraction, Moving along } );

       table keep selected rows ( t1, [Inhabitants]>=1000000 );



       echo("Original table:");

       table list ( t1 );



       echo("Create a modified table:");

       table copy table ( t1, t2 );

       table sort rows ( t2, City );

       table delete selected rows( t2, [City]==Los Angeles);

       table delete columns( t2, Country );

       table insert columns( t2, Time Zone, "", 3 );

       table append( t2, {{ Shanghai, 23000000, "Jing'an Temple", "UTC+8", Bicycle, Bla }} );

               // Last entry 'Bla' lies outside the colums with headers and is considered an orphan.

       [t2:City,Paris,Famous attraction] = Moulin Rouge;

       [t2:City,Paris,Time Zone] = "UTC+1";

       [t2:City,New York City,Inhabitants] = 8323340;

       table list ( t2 );



       echo("Lets compare these tables:");

       rv[] = table compare( t2, t1, report, City, different rows );

       echo("Return value from comparing: ", rv[] );



       // Split statistics out in order to fit output onto the page.

       table split table columns( report, statistics, {Statistics Parameters, Statistics Values} );

       table delete blank rows( report );



       echo("Comparison Report:");

       table list ( report );

       echo("Statistics:");

       table list( statistics );

Output

Original table:
    0 : City          | Country | Inhabitants | Famous attraction  | Moving along 
    1 : New York City | USA     | 8300000     | St. of Liberty     | Taxi         
    2 : Philadelphia  | USA     | 1500000     | Independence hall  | PCC streetcar
    3 : Montréal      | CAN     | 1700000     | Hôtel du Parlement | Skidoo       
    4 : Los Angeles   | USA     | 3800000     | Hollywood          | Car          
    5 : Vienna        | AUT     | 1800000     | Prater             | Fiacre       
    6 : Bangkok       | THA     | 8250000     | Wat Phra Kaeo      | Longtail boat
    7 : Paris         | FRA     | 2240000     | Eiffel Tower       | Métro        

Create a modified table:
    0 : City          | Inhabitants | Famous attraction  | Time Zone | Moving along  |    
    1 : Bangkok       | 8250000     | Wat Phra Kaeo      |           | Longtail boat |    
    2 : Montréal      | 1700000     | Hôtel du Parlement |           | Skidoo        |    
    3 : New York City | 8323340     | St. of Liberty     |           | Taxi          |    
    4 : Paris         | 2240000     | Moulin Rouge       | UTC+1     | Métro         |    
    5 : Philadelphia  | 1500000     | Independence hall  |           | PCC streetcar |    
    6 : Vienna        | 1800000     | Prater             |           | Fiacre        |    
    7 : Shanghai      | 23000000    | Jing'an Temple     | UTC+8     | Bicycle       | Bla

Lets compare these tables:
Return value from comparing: different
Comparison Report:
    0 : City          | Inhabitants | Famous attraction | Time Zone | Moving along | Change Remarks
    1 : New York City | 8323340     |                   |           |              | Data modified 
    2 : Paris         |             | Moulin Rouge      | UTC+1     |              | Data modified 
    3 : Shanghai      | 23000000    | Jing'an Temple    | UTC+8     | Bicycle      | Row added     
    4 : Los Angeles   | [3800000]   | [Hollywood]       |           | [Car]        | Row deleted   

Statistics:
    0 : Statistics Parameters                        | Statistics Values
    1 : New Table number of columns                  | 5                
    2 : Old Table number of columns                  | 5                
    3 : Columns added                                | 1                
    4 : Columns removed                              | 1                
    5 : Columns rearranged                           | 2                
    6 : New Table number of rows                     | 8                
    7 : Old Table number of rows                     | 8                
    8 : Rows matching                                | 6                
    9 : Rows equal                                   | 4                
   10 : Rows added                                   | 1                
   11 : Rows removed                                 | 1                
   12 : Rows rearranged                              | 6                
   13 : Values equal                                 | 16               
   14 : Values added                                 | 3                
   15 : Values removed                               | 3                
   16 : Values modified                              | 2                
   17 : New table found orphan columns               | Yes              
   18 : Old table found orphan columns               | No               
   19 : New table non-unique orientation identifiers | 0                
   20 : Old table non-unique orientation identifiers | 0

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

table compare ...

Function Names

Description

Call as: procedure or function

Restrictions

Parameter count

Parameters

Return value

Examples

Output