B4P Reference

Function Names

table validate

Description

This function checks and validates the target table against a rules table which contains values, rules (comparison expressions) and optionally more advanced verification expressions. This function can check for the following:

Contents in selected columns comply with rules specified (e.g. is equal to, contains , etc. )
Optionally, the number of rows in the target table is checked (for minimum required, maximum allowed, etc.)

This function requires at least 3 parameters:

The names of both tables (target and reference etables), and
The columns to be checked (target data in target table, checking comparison expressions in the reference table).

In the target table, the following four columns will be added with header names as listed below if they are not yet existing. If they exist, contents will overwritten, and "Explanations" appended.

Row Result	Comparison result statement related to particular row. See validating tables row results.
Group Result	Result related to entire group See validating tables group results.
Explanations	Details about mismatch. In case multiple mismatches, exist, only the first mismatch will be explained
Ref Row	Identified row number in the reference table where the comparison has been made.

The reference table contains rows which are being used to validate the contents in the target table. For entries in the target table, 1 or more corresponding rows may be specified in the reference table. As long all conditions in the columns are met in at least one of the rows, then the validation result is OK.

Note: The sequential order of the rows in the target table as well as in the reference table do not matter. Grouped rows do not need to lie in blocks of adjacent rows and may be scattered throughout the entire table.

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 table contents

Parameter count

4 - 7

Parameters

No.

Type

Description

1.
input

string

Name of target table

This table will be checked and validated

2.
input

string

Name of reference table

The table contains the rules to apply for the validation procedure

3.
input

table columns

Columns with data to be validated (both target and reference tables)

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
The header names and column numbers are applicable to both target and reference table.

The columns in the target table containing values to be checked will be checked against the corresponding columns in the reference table containing comparison expressions. The columns do not need to be in the same location or order if they are referenced by their header names.

In the target table, the columns where the data needs to be checked must be selected. In the reference table, the checking rules (contains comparison expressions) are located in the corresponding columns.

Opt. 4.
input

table columns

Columns with Row Match Identifiers (both target and reference tables)

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
The header names and column numbers are applicable to both target and reference table.
No values can be provided by using an empty set {}

In the reference table, only the rows with the same row match header will be compared instead of the entire table.

Default value: {} (no columns specified)

Opt. 5.
input

table columns

Columns with Group Identifiers (in the target table)

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
The header names and column numbers are applicable to both target and reference table.
No values can be provided by using an empty set {}

Default value: {} (no columns specified)

Opt. 6.
input

table columns

Columns with Group Match Identifiers (target and reference table)

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
The header names and column numbers are applicable to both target and reference table.
No values can be provided by using an empty set {}

All rows with the same Group Identifiers are allocated to one group. This parameter actually selects the rows in the reference table to be used for comparison.

Default value: {} (no columns specified)

Opt. 7.
input

table column

Columns with Group Match Identifiers (in the reference table)

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 may be specified
The header names and column numbers are applicable to both target and reference table.
Specify an empty set {} to skip this parameter.

The contents below this header contains B4P expressions which must reurn boolean results. As this function provides a table context for partial table specifications providing current target table name and table row, the expressions in the specified table column may make use of this where only the columns need to be specified.

Default value: {} (no columns specified)

Opt. 8.
input

table column

Column with Comparison Directions (reference table) for validation of within a group

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 may be specified
The header names and column numbers are applicable to both target and reference table.
Specify an empty set {} to skip this parameter.

In the reference table, the data below this header may contain the following values:

min 0	This row may be checked 0, 1 or multiple times. This option imposes no restrictions on how many time the comparison is made. It is the default setting if this parameter is not supplied to this function.
equal 0	This row must not be checked at all. Error asserted if this row has been matched. Useful to check for table entries which are not supposed to exist.
min 1	This row must be checked at least once or more
equal 1	This row must be checked precisely once
max 1	This row may be checked 0 or 1 times

Default value: {} (no columns specified) - Value 'min 0' applies for all rows

Opt. 9.
code

expression
:string

Expression to select rows

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), ....

Return value

Type	Description
boolean	Table is OK true if no mismatches identified. Otherwise false.

Examples

      table initialize( target, {{ Name, Gender}, { Philippe, male }, { Pascale, female }, { Michele, female }} );

      echo("Target table:"); table list ( target );



      table initialize( reference, { Gender, male, female }  );

      echo("Reference table:"); table list ( reference );



      res[] = table validate( target, reference, Gender );



      echo("Target table after validation (result = ", res[], "):");

      table list ( target );



      echo("Add a mistake.");

      [target:Name,Philippe,Gender] = "Male"; // Upper case deviates



      res[] = table validate( target, reference, Gender );



      echo("Target table after validation (result = ", res[], "):");

      table list ( target );



      // See following sub-sections for more sophisticated examples.

Output

Target table:
    0 : Name     | Gender
    1 : Philippe | male  
    2 : Pascale  | female
    3 : Michele  | female

Reference table:
    0 : Gender
    1 : male  
    2 : female

Target table after validation (result = true):
    0 : Name     | Gender | Row Result | Group Result | Explanations | Ref Row
    1 : Philippe | male   | OK         | OK           |              | 1      
    2 : Pascale  | female | OK         | OK           |              | 2      
    3 : Michele  | female | OK         | OK           |              | 2      

Add a mistake.
Target table after validation (result = false):
    0 : Name     | Gender | Row Result | Group Result | Explanations         | Ref Row
    1 : Philippe | Male   | Mismatch   | Not OK       | [Gender] = 'male'?   | 2      
      :          |        |            |              | [Gender] = 'female'? |        
    2 : Pascale  | female | OK         | Not OK       |                      | 2      
    3 : Michele  | female | OK         | Not OK       |                      | 2

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