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

Alteryx designer Discussions

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

Read dynamic rules defined and apply appropriate filter

Asteroid

I'm trying to implement a Rules Engine. The rules are stored in a excel file in the format mentioned below. Its similar to rules in the SQL Where clause. In the table below there can be multiple rules defined. In a single rule there can be mulitple Operands defined. eg; In Rule 1 currently two operands are used, there can be 10 operands. I'm trying to see how can I read this Dynamic columns in Alteryx and process it. Here is the approach I'm struggling to implement currently.

 

  1. Read the Rules list from the excel file. 
  2. Iterate through the rows
    1. In the same iteration, iterate through the column until the end of the column(ERROR_CODE).
  3. Form a WHERE clause like SQL, maybe use Alteryx In-DB using the rules and generate the output as a csv with the list of Claim_numbers that conform to that rule.

 

Can you please let me know if there is a tool/ a better approach to solve this problem. I'm trying to find For-each loop function in Alteryx to iterate through the rows/ columns, but there seems to be none.

 

 

Rule_NumberTABLE_NAME_1FIELD_1OPERAND_1VALUE_1LOGICAL OPERATORTABLE_NAME_2FIELD 2OPERAND 2VALUE_2ERROR_CODE
1CLAIMSBILLED_AMOUNT> 100ANDCLAIMSPAID_AMOUNT <200 Billed greater than paid
2CLAIMSPAID_DATE- 3ANDCLAIMSclaim_receipt_date>60Pd Date greater than 60 days
Bolide
Bolide

there isn't a loop function in alteryx, but there are Batch Macros and Iterative Macros that can run different criteria with each record. 

 

However, you can accomplish your goal in a variety of ways without touching either of those macros. So to clarify, you want to keep a set of rules in excel which will then be brought into alteryx and applied to specific tables/fields? Is there any reason you can't just write the rules within alteryx as a field? 

 

I've attached a workflow that creates the expression from your data provided. You can use the expression in a batch macro if you'd like or dynamic function - See @patrick_digan's post here for more info on dynamic formulas. https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Dynamic-Replace/ta-p/121655

 

i'd likely wrap in a batch macro to change a formula if i HAD to go this route. Otherwise, a few simple filters would suffice, although they won't be quite as dynamic. 

 

Note, you can actually make the operators unlimited if you transpose this table, but that's an enhancement that may not be needed at this point. 

 

EDIT: I remembered where the Dynamic Formula was from. CREW MACROS - download here: http://www.chaosreignswithin.com/p/macros.html

Asteroid

Thanks a lot Jarrod! I was able to replace the WHERE clause with the concat_formula that you generated. I connected it to SQL database to query. However I get this error because of the single quotes for values. eg: there are no single quotes around 'K08%'. I tried to use regex_replace the double quotes, but I think the dynamic input is generating quotes on its own. Can you please let me know if there is a better approach?

 

Capture.PNG

Labels