topic Re: Data Quality using a list of business rule in Alteryx Designer Desktop Discussions

Data Quality using a list of business rule

Meena — Tue, 08 Oct 2019 20:26:03 GMT

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

Re: Data Quality using a list of business rule

BrandonB — Tue, 08 Oct 2019 20:36:23 GMT

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

Re: Data Quality using a list of business rule

BrandonB — Tue, 08 Oct 2019 20:38:05 GMT

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.

Re: Data Quality using a list of business rule

DiganP — Tue, 08 Oct 2019 20:42:28 GMT

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

Re: Data Quality using a list of business rule

danilang — Wed, 09 Oct 2019 17:17:08 GMT

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

Re: Data Quality using a list of business rule

BrandonB — Wed, 09 Oct 2019 17:28:22 GMT

@danilang awesome solution!

Re: Data Quality using a list of business rule

danilang — Wed, 09 Oct 2019 17:29:17 GMT

Thanks @BrandonB

London? Maybe we can meet for a beer.

Dan

Re: Data Quality using a list of business rule

BrandonB — Wed, 09 Oct 2019 17:31:20 GMT

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

Re: Data Quality using a list of business rule

danilang — Wed, 09 Oct 2019 17:32:35 GMT

@BrandonB

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

Dan

Re: Data Quality using a list of business rule

BrandonB — Wed, 09 Oct 2019 18:01:45 GMT

Unfortunately not 😞

Re: Data Quality using a list of business rule

Meena — Thu, 10 Oct 2019 18:56:23 GMT

Hi ,

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

Please can you attach the macro too?

Thanks

Re: Data Quality using a list of business rule

BrandonB — Thu, 10 Oct 2019 18:59:22 GMT

@Meena it looks like @danilang has included the yxzp file at the bottom of their post which you should be able to open up in Alteryx. The Rule Validator macro is in the middle of the workflow.

Re: Data Quality using a list of business rule

Meena — Thu, 10 Oct 2019 19:07:48 GMT

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

Re: Data Quality using a list of business rule

Meena — Thu, 10 Oct 2019 19:37:46 GMT

Brandon.. I use Alteryx version 2018.4 which is unable to import Dan's package 😞

Re: Data Quality using a list of business rule

BrandonB — Thu, 10 Oct 2019 19:52:51 GMT

I edited the Alteryx version in these files. Can you give it a try now? You will need to download both files first. Then you will notice in the BusinessRuleChecker file that there is a box with a question mark when you open it up. You will need to put the RuleValidator macro in this spot and reconnect the connections.

Re: Data Quality using a list of business rule

Meena — Thu, 10 Oct 2019 21:48:06 GMT

Hi Dan,

When I add new rules and change the input I get an error saying "The field schema for the output "Output13" changed between iterations. I guess this is from the macro.

This is first time I am using Alteryx macros would you be able to spare some time in explaining what each step in your macro is destined for?

Thanks

Re: Data Quality using a list of business rule

Meena — Thu, 10 Oct 2019 22:03:50 GMT

Thanks Brandon. I was able to download.

Regards
Meena | Data Architect, Partner Oversight, Small Merchant Acquistions
ponmeenal.balasubramanian1@aexp.com |18850 North 56th Street, Phoenix AZ 85054 | Phone : 602-537-5757

American Express made the following annotations

"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."
American Express a ajouté le commentaire suivant
Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué et peuvent contenir des renseignements confidentiels et privilégiés. Si vous n'êtes pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces jointes. Merci.

Re: Data Quality using a list of business rule

danilang — Fri, 11 Oct 2019 10:41:36 GMT

Hi @Meena

You're correct in that the error message is caused by the macro. Specifically, the fields in the output have changed between one iteration and the next. You can change this behaviour by opening the macro and selecting View->Interface Designer from the menu. Click on the Gear icon on the left and check "Output fields change based on macros'..." and select "Auto Configure by Name..." as shown below

Save the macro, and run the main workflow. The macro should run to the end now.

However, this error is caused by a difference in structure between the sample data you provided originally and the data that you're running with. Allowing the macro to proceed may not give you the correct results. Validate the results and if they don't agree with what you're expecting, can you attach the Validation rules.xlsx that causing the error?

@BrandonB Thanks for all your help

Dan

Re: Data Quality using a list of business rule

Meena — Thu, 17 Oct 2019 17:24:28 GMT

Dan... Your solution is awesome and simple.

As I'm new to using macros and control parameters I'm understanding your solution and customizing to suit my requirement.

The volume of my date & # of business rules is huge.

Hence I will have to test it to see on the time consumed if we do field by field (transpose of data) validation.

Meanwhile if you think of anything which we can do without transpose kindly let me know.

Thanks a lot.

Re: Data Quality using a list of business rule

Meena — Thu, 17 Oct 2019 21:59:35 GMT

Brandon,

Are you with Alteryx?

Would you be able to guide me on Dan's solution and help me understand the macro in depth?

I'm replicating Dan's solution and trying to customize it, but unsuccessful so far..

Have never designed any macros until now and keep getting errors on the action tool 'root elements missing' etc.

We can connect via webex if you would be able to help me out.

Thanks