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

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

In addition when I customize I get errors in the dynamic replace tool as below.

Any suggestions how to resolve it?

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

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

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

[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/3... . 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

[Meena]

Thanks Dan..Will work on it to fix the errors.

I use an older version of Alteryx hence unable to open the package.

Please could you upload the workflow and macro separately.

[danilang]

Hi @Meena 

Here they are

For future reference, Alteryx workflows are stored as XML files.  Open the workflow(.yxmd) or macro(.yxmc) with Notepad and change the second line in each to match your version of Alteryx. 

Change 
<AlteryxDocument yxmdVer="2019.2">
to 
<AlteryxDocument yxmdVer="2018.4">

This won't work on the package(yxzp), since it's a renamed zip file so you'll have to extract the files and then change the XML.

Dan 

[Meena]

Dan, 

Thanks for sharing the code..

I was replicating your solution and stumped upon using [Rule Description] which is not appearing in my macro (though I mapped it as a control parameter while calling the macro..

I want the result to be like [Name} + " is Valid" or [Name] + " is " + [Rule Desc] instead of hard coding...

Any idea what I am missing?

[danilang]

Hi Meena

Change the Data Quailty formula in your macro to 

if ![OutputResult] then [Name] + " is Valid "
else [Name] + " is Blank "
endif

and set the action tool to replace the blank

Also in your Action tools in general, you need to ensure that the action is replacing the string in the proper place.  When I opened the 1st action tool in your macro the configuration was set to this

Notice how the highlight is on Filter (11).  There are lots of possible places where the string "AIN Number" could be in the filter definition and it's up to you to tell the Action tool which attribute it should target.  Drill down into the configuration and highlight the line where the operand is actually located.  For filter tools this is particularly confusing since the string is actually in two places depending on if the Filter is Basic(which translates to Simple in the tree(Don't get me started!!!)) or Custom which uses the 1st Expression line in the tree. 

Long story short, your Action tools should highlight the row you want to to change, like this for your 1st filter tool.  

Dan

[Meena]

Dan.. I'm sorry for not being clear.

I want to use the [Rule Desc] as dynamic value instead of  hard coding "blank" or "valid".

However I am not getting that variable , [Rule Description] in Data Quality formula.

I have mapped in the workflow calling the macro.

So looks like I am definitely missing it.

I will go though your suggestion on AIN Number and understand.

Thanks for your time and support.