Alteryx Design Pattern: Validation Testing to Check for Data Completeness

For today’s design pattern, Chris is going to show you how using a combination of joins, filter and append tools can be use to perform a validation test in the workflow to ensure that data doesn’t flow downstream if the test condition is not met.

Why is this important

When building a workflow testing tools are important to ensure consistency and completeness of the data as it is processed. With the base testing tools in Alteryx, it’s possible to build tests to validate your data, but what if you don’t know how many records you are expecting. You can’t hardcode the test criteria into the workflow. So this design pattern shows how you can use a specified datapoint as the parameter for the test.

Let’s jump in and take a look

In this example, Chris is using the results from an API (in this example it’s a listing of the directors listed on Companies House at a UK company). This data is dynamic as new Directors could be appointed at any time.

In the API results (these were pre-obtained just so we can focus on the design pattern), it shows that there are 50 listed Directors.

The data is prepared and transformed using a cross-tab tool to make the table which we require as the output. In this we can see that the table has 50 rows of data. This is where the design pattern comes in;

• First Chris filters out the results line in the API data to get one record with the value of results expected.
• Then using a record count tool as a breakout flow from the main workflow get a field showing the count of records upstream
• These two data points are joined together using record position (we can use this as we know there’s only 1 record flowing out the output anchor of these tools, plus there isn’t a common field to join on)
• Then we build in a condition to test if the [Count] = [JSONStringValue]
• Connected to True output anchor is then a append tool. Here we need to connect to the Target input anchor (this is the important bit in the design pattern). If no data flows into the input there is nothing for the data which comes into the Source input to append onto. Within the select tool we deselect all the target fields as we don’t need these down stream, as they were only used for the purpose of the validation test.

Not shown in the video is you could add a message tool to the false output of the filter tool to send a error message to the user if the [Count] != [JSONStringValue]