Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Data Quality using a list of business rule

Meena
8 - Asteroid

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

37 REPLIES 37
BrandonB
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

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

DiganP
Alteryx Alumni (Retired)

@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
danilang
19 - Altair
19 - Altair

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

 

 

 

 

 

 

 

BrandonB
Alteryx
Alteryx

@danilang awesome solution!

danilang
19 - Altair
19 - Altair

Thanks @BrandonB 

 

London?  Maybe we can meet for a beer.

 

Dan

BrandonB
Alteryx
Alteryx

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

danilang
19 - Altair
19 - Altair

@BrandonB 

 

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

 

Dan

BrandonB
Alteryx
Alteryx

Unfortunately not 😞

Labels