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]

That is Data Quality formula to be

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

[danilang]

Hi @Meena 

That part of the formula is updated by the last Action tool.  The rule, formula and description change with every iteration of the macro and the Action tool replaces the string "Blank" with the correct rule description that is currently being evaluated.  The Rule Description isn't available in the formula tool, because it's passed in as a control parameter and not as part of the data.  When I ran the workflow with your sample data all the rules passed so there was never a case where this was tested.  Replace a business name with AAAA in your input and run the workflow again.

Dan

[Meena]

Dan... Thank you for patience in explaining.

I now understand on the Rule Desc field not appearing in the formula field...

I am still understanding how the macro works... because if the same field is having 2 rules (like Merchant DBA Name) cannot be blank or have repeating... it appends 2 results like 'Merchant DBA Name Valid,Merchant DBA Name Repeating'.

Hope I didnt mess up anything...

Attaching is your solution macro with the input rules I used 😞

[Meena]

Hello Dan, I figured out how the macro can take result from the Rule.

Thanks

[Meena]

Dan.. I'm back again wishingif I could borrow your Alteryx expertise to an issue I'm trying to resolve.

As an example Merchant DBA Name -- I have 2 separate rules : Blank or Repeating an I've capture them indvidually : 2 iteration, one for each rule..

When we capture individual column result it concatenates results from both iterations...Merchant DBA Name Quality

Attached workflow with example...

Any idea how we can include only the result from 1 iteration (when not valid?)

[Til22]

Hi Dan,

Thank you so much for your business rule validation workflow, it has helped me out enoumously.

I just had a question, how would you handle it if you had to parse a different validation based on a value in another column.  For example if the Merchant's [payment type] was credit card then validate the [Card Number] field is not blank, but for all other payment types it is valid to have a blank [Card Number] field?

I"d love to hear your thoughts.

[sai11]

@danilang 

when i have formula say [Value]>5 for merchant number then i get a error saying "> invalid" , in dynamic replace tool, any idea on how to resolve this issue,

i am following your solution but my formula contains comparisons operations.

Thanks

sai

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