Searching a Specific Excel File for a String Produced by a Formula in an Alteryx Workflow

Question

The workflow I've designed has a formula which extracts a string from the inputted data. I also have an Excel file that has a one-column list of values. How do I "configure" my Alteryx workflow to validate whether or not the string the formula has produced exists in the Excel file?

Example: The formula produces a character string representing the Province. The Excel file has a one-column list of "valid" two-character Province Codes (i.e. BC, ON, QC, etc.). I'm looking for a way to determine whether or not the string produced by the formula exists in the Excel list.

Also, is using the "external" Excel file/list the best way of doing this, or can I eliminate the need for the Excel file, and use Alteryx to do the search/lookup/validation?

THECUSE4463 · Answer

Being extremely new to Alteryx (i.e. this is the very first workflow I've designed), I'm still not sure exactly how to proceed. I've attached a file which shows the workflow as it currently exists, which I hope will allow you to answer the following question:

If the TEXT INPUT Tool lists the valid two-character Province codes and the FORMULA Tool pulls the Province Code from the data it is reading, how (i.e. which Tools, linked in which way) do I configure my workflow to determine if the result of the Formula exists in the List.

Alteryx Example.docx

Clifford_Coon · Answer

Hi @THECUSE4463 ,

My suggestion: the province codes are unlikely to change so a text input should suffice. I would go for an excel file if there is a likelyhood of the codes changing.

You can use a combination of a join and either message or test tool to report the error.

Happy Solving ;-)

Province_Test.yxmd