Data Quality using a list of business rule

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

Validation Rules.xlsx

Data Investigation

Accepted answers

danilang

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

BusinessRuleChecker.yxzp

All comments

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

Validation Rules.png

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.

validation rules workflow.yxmd

danilang

Hi @Meena

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 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

BusinessRuleChecker.yxzp

BrandonB

@danilang awesome solution!

Meena

Hi ,

I would like to test your solution which might work for me.

Please can you attach the macro too?

Thanks

Meena

Dan... Apologies... I use an Alteryx version 2018.4 hence unable to download your package.

Please could you guide me how I get your solution to test it out?

Thanks

Meena

Brandon anyway we can input which fields should be selected for the multi field formula from Validation Rules (Rather than we manually select?

Meena

Sample which fails in the dynamic replace with

Tool #17: RecordInfo::CreateRecord: A record was created with no fields.

Tool #17: Parse Error at char(0): Contains requires 2 or 3 arguments.

Alteryx Data Quality Rules.xlsx

BrandonB

Hi Meena,

The challenge with using a macro is ensuring that the data you pass into it stays relatively consistent over time (columns). Although a multi field tool wouldn't automatically apply to new columns dynamically like a macro would, you could check the boxes for which fields you want certain formulas to apply.

This is a Tool Mastery article on Analytic Apps and Macros: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Apps-and-Macros/ta-p/35375 . It reviews the situations when you would want to use them. I'm not sure if troubleshooting the macro provided in this thread is worth your time if your data will continue to produce inconsistencies. Having a bit more control over the formulas being executed on each field seems like it might work better for you in this situation. Macros are definitely powerful though and I would recommend continuing how to use them!

danilang

Hi @Meena

i had a chance to run the data through the workflow and fix the various errors. These fell into 3 classes

1: "field schema changed between iterations". Occasionally the Result field in the macro would change from one numeric type to another. This doesn't affect the results but it did stop the macro from completing. I change the configuration of the macro to allow this kind of schema change so it now completes

2: Formulas that referenced columns that don't exist in the input data. These cause various errors since the formula can't be evaluated due to the missing column. I fixed this by modifying the start of the workflow to only pass Formulas that referenced existing fields

The Field Info tool (in red) outputs a list of the field names from the connected tool. I joined this with the Validation rules on Field Name to exclude any formulas that would fail because of the missing input field

3: Formula errors. There were a few of these where the text that is in the formula tool is not valid Alteryx syntax. For instance the second formula for Merchant Physical Location was this contains([Value],'po box','p o box','pob') This isn't a valid formula. The Contains(field,value) function only accepts one value argument. I changed this one to "contains([Value],'po box') or contains([Value],'p o box') or contains([Value],'pob')". There are others that I didn't fix since they were never used either because having the target field missing or the Validation flag set to false. Left as an exercise for the student 🙂

The way you can check these formulas is to create a new work flow with some sample data and add a formula tool directly after the input. Copy each of the validations formulas from your spreadsheet into this formula tool in turn, run it and check the results. For contains([Value],'po box','p o box','pob') you get a syntax error directly in the formula tool. You'll get an error for the Merchant URL formula as well since IF MCC=5969 then isempty([Value]) end just isn't valid. Make sure you include sample data to check the positive and the negative case for each of your formulas.

One other thing. Having the line break in the field name for Validation Rule Description and Validation Formula fields caused some errors in macro field mapping screen, so try to avoid line breaks in field names

Dan

BusinessRuleChecker.yxzp

nelsonmatt

Hi - I have a qq with the text input tool.

from the canvas I am bringing in 5 dq checks fields and some 60 data lines.

In the text input tool of the macro, I only see one line of data entered as input test. I am assuming the macro automatically iterates through all the 60 lines despite only one data line mentioned in the input text. is that true?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703