Skip to main content

Expect_Equals_ico.png Expect Equal Tool

Use Expect Equal to test if two data streams are identical and report errors if not.

The tool reports only the first four errors it finds and gives the count of additional errors.

Expect Equal can tell:

  • If field names are different or out of order.

  • If field types are different.

  • If numeric, string, or date-time values do not match.

  • If blob or spatial values do not match.

  • If the number of rows is different (more or fewer than expected).

Tool Components

Expect Equal has 2 anchors:

  • Expected anchor - connect the data you expect to this anchor.

  • Actual anchor - connect the data you want to validate to this anchor.

The significance of the two anchors is related only to the messages the tool gives. The tool may report:

Error: Expect Equal (1): Record 1 field Field1: Expected "a", Actual "A" 

The value that is expected is simply the one that came in on the expected anchor.

Configure the Tool

This tool has no configuration options.

Tool Examples

Examples can all be demonstrated with the following workflow:

Expect_Equals_ex1.png

1. Example - Fields differ 

If the fields available in the Expected and Actual inputs are different, you will get messages

  • Error: Expect Equal (1): Expected field was missing: F1 

  • Error: Expect Equal (1): Actual field was not expected: Field1 

Looking at the two messages, you can determine if you were expecting the wrong field name or the data produced the wrong field name.

Note that field name comparisons, as always in Alteryx tools, are case-blind, so “F1” matches “f1”.

2. Example - Fields are in a different order 

If the two streams have the same field names, but they come in a different order, the tool will show a subset of all the fields to show the difference:

Error: Expect Equal (1): Different field order. Expected: ..., b, c; Actual: ..., c, b 

If fields come in a different order the data will be compared by field name, not by position. So if field B has value 2 in both streams the tool will not report an additional error. However, if the expected field B has a value 2 and the actual field B is 3, that would report an additional error.

3. Example - Fields are different types 

Type matching is expansive. All integer types match all other integer types, Float matches Double, and all string types match all other string types. Fixed Decimal only matches Fixed Decimal, to preserve precision in the comparison of values. Sizes of types are not counted in the type matching.

  • Expecting a float type but getting an int type will report an error in type checking and it doesn't compare the values.

  • When types differ, the error message is

    Error: Expect Equal (1): Field a is expected to be type Double, but is actually type V_String 2147483647 

    Note that the type descriptions include the exact type name and some size information but the size doesn't cause a mismatch.

4. Example - Data values differ 

  • Integer values are compared without loss of precision. It can detect the difference between 9,223,372,036,854,775,807 (the largest possible 64-bit integer) and 9,223,372,036,854,775,806.

    Error: Expect Equal (1): Record 1 field F1: Expected 9223372036854775807, Actual 9223372036854775806 

  • Floats and doubles are compared for exactly equal, with no option for “almost equal”.

    For example, the value 5124095575370702.5 matches 5124095575370701.6, because the representation of doubles does not have enough precision to distinguish between these values, they are stored identically in the computer. When numbers don’t match, they are printed with fixed 17 digits.

    Error: Expect Equal (1): Record 1 field D: Expected 5124095575370702.00000000000000000, Actual 5124095575370701.00000000000000000 

  • String types are compared as case-sensitive. The values are shown if they don’t match the expected, actual strings may be shortened to just show a little context before and after the first difference:

    Error: Expect Equal (1): Record 1 field a: Expected "...ar like the present peri...", Actual "...ar like the Present Peri..." 

  • Date/Time values are compared to the full precision of the most precise of the two and the values are shown if they differ:

    Error: Expect Equal (1): Record 1 field c: Expected 2024-03-14 11:07:00, Actual 2024-03-14 11:07:00.001

    Error: Expect Equal (1): Record 1 field c: Expected 11:07:00.002, Actual 11:07:00.001 

  • If blob fields differ the tool simply reports that they are different:

    Error: Expect Equal (1): Record 1 field a: Expected Blob does not match Actual 

  • Spatial objects are converted to GeoJson and compared like strings:

    Error: Expect Equal (1): Record 1 field d: Expected "...inates":[1,2] }", Actual "...inates":[1,2.000001]}" 

5. Example - Different number of rows 

The tool can report:

Error: Expect Equal (1): Actual had fewer records than expected: 2 

Error: Expect Equal (1): Actual had more records than expected: 3 

The number reported (2 or 3 here) is the number that is different from expected.

6. Example - Too many messages 

The tool only reports the first 4 messages. If there are more it will conclude:

Error: Expect Equal (1): Total errors: 6 (suppressed messages beyond limit 4) 

In this section: