Data Quality using a list of business rule

[Meena]

Hi

I a working on a requirement where I have validation rules built for a set of columns.

Instead of writing formula on each field I want to keep it dynamic so that when the rules changes there will less code change.

The field Names should be read from "Validation Rules" sheet and  appropriate validation rule checked against the data to produce the output result.

I have attached a sample of how the rules will look like and what the output is expected to be "<Field Name> Quality".

Please can you share ideas on how this can be done?

Thanks

[BrandonB]

You will probably want to use a Multi Field Formula tool with repeatable logic so you can just check the boxes of all of the columns that use the same rules. Please see below as an example

 

[BrandonB]

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. 

[DiganP]

@Meena @BrandonB's approach is the most straightforward. If you want to create a summary towards the end, you can use the formula tool concatenate it. Attached is the workflow. 

[danilang · Accepted solution]

Hi @Meena 

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 

Dan

[BrandonB]

@danilang awesome solution!

[danilang]

Thanks @BrandonB 

London?  Maybe we can meet for a beer.

Dan

[BrandonB]

Irvine California, but will definitely reach out if I travel international!

[danilang]

@BrandonB 

I meant are you going to London for Inspire Europe next week?

Dan

[BrandonB]

Unfortunately not 😞