community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Data Quality using a list of business rule

Meteor

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

Alteryx
Alteryx

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

Alteryx
Alteryx

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. 

Alteryx
Alteryx

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

 

Dataset.PNG

Digan
Alteryx
Nebula
Nebula

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.  

 

f.png

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 

 

t.png

 

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.

 

m.png

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. 

 

w.png

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 

 

results.png

 

 

 

Dan

 

 

 

 

 

 

 

Alteryx
Alteryx

@danilang awesome solution!

Nebula
Nebula

Thanks @BrandonB 

 

London?  Maybe we can meet for a beer.

 

Dan

Alteryx
Alteryx

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

Nebula
Nebula

@BrandonB 

 

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

 

Dan

Alteryx
Alteryx

Unfortunately not

Labels