How To Use The Comparison Editor In LegiTest

These steps and video demonstrate how to use the Comparison Editor in SentryOne LegiTest to compare results from different queries. This can be used to extract info from two databases, compare them, and make sure it matches.

Step 1: Right-click one of your test groups, go to Add Test - Data Management Interactive Comparison. This opens up the Interactive Comparison Editor.

Step 2: Put your expected query at the top using the drop-down. We will us our HigherEd Connection.

For this example, we will Select from the Student Table and want the Student ID, First Name, Last Name, Birth Date.

You can format your SQL statement using the 4th icon.

Step 3: Use the green arrow to Execute the SQL statement and see what results you receive.

Step 4: Go to your actual query on the right side. We used our HigherEd DW connection.

Step 5: This should include the same student information in the Dim_Student table. Retrieve your StudentAK, which is the alternate key and the same as the Student ID. This makes it easier to compare records. Now enter the same columns: First Name, Last Name, Birth Date.

To make the names the same, since they are different with the underscores included, you can alias them. You won’t have to do this with the StudentAK because we are setting this as a key column, which will match it up automatically.

Step 6: Click the Format SQL icon and execute the query.

To compare the results, you’ll want to set the StudentID as the key. You can do this by going to the expected query and right-clicking the column header and selecting the first option from the context menu.

The light grey indicates which columns have the same value. We can look at our summary which tells us we have one extra row, which is expected.

We are only comparing using the key. To change this, click on the Comparison tab - Auto map comparison columns.

Step 7: Select Compare.

To get rid of the ‘Unknown’ row which appears as a difference, go to the Configuration - Ignore rows in actual set only.

The data should now be presented in a desirable way.

Step 8: Click OK. Clicking OK will add a new test and all the necessary components of that test. You can edit these components by going in and selecting each individually. You can also select the white gear icon in the corner of ‘Comparison Manifest’ to open the Interactive Comparison Editor and make additional modifications.

 

Download a Free Trial