This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have attached an example workflow as reference. By simply checking the boxes of the columns that you want to apply the validation rules to, it will dynamically add "Quality Check" as a suffix to new columns that are created, allowing you to quickly apply these rules to other columns as needed.
Here's a solution that runs all your business rules and then tabulates the results. It's dynamic in that if you change the rules in the Validation Rules tab, it will apply these new rules to your data set. The solution is built around the Dynamic Rename tool, which is unique in that it has the ability to interpret formulas passed to it as part of the replace process. In order to get this to work, you have to modify the rules tab of your input somewhat.
The formula field is a Alteryx compatible formula, i.e. it will run if you put it in a Formula Tool so
"' or contains PO or P.O." becomes "isempty([Value]) or contains([Value],'PO') or contains([Value],'P.O.')"
The field that's referenced in the formula is always [Value] since the data is transposed before the rules are validated, like so
The macro that applies the formulas to the data is a batch macro, so it applies the values in the control parameters one row per iteration to all the data in the data input.
The first filter tool passes through all the rows that have the field name from the parameter, i.e. 1st iteration is "Merchant Number" second is "Merchant Name", etc. This is why I transposed the data. It's much easier to dynamically select rows than columns. The next Formula updates rule that being checked in this iteration and that's passed to the dynamic replace which evaluates the rule in the Formula field. The final formula tool creates the rule result description for the row, which eventually ends up as a column after it's Cross Tabbed in the Main routine.
The tools after the Rule Validator macro just Cross tab the results and build the Merchant Data Quality summary column before joining back to your main data resulting in