Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Read dynamic rules defined and apply appropriate filter

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



2CLAIMSPAID_DATE- 3ANDCLAIMSclaim_receipt_date>60Pd Date greater than 60 days
12 - Quasar
12 - Quasar

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.


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:

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