Excel Controlled Dynamic Field Iterative Macro Filter / Formula Challenge

[iCFO]

Basically, I am trying to build an iterative macro that uses a UI friendly excel controller to populate iterations of a complex "If Statement" or "Filter Criteria" as well as apply field changes by iteration via the Formula Tool. Initially I am just looking for some feedback on feasibility to ensure that I am taking a viable approach to the build.

I am currently doing a basic table join approach to making changes. I am not really interested in this approach as it is very limited in the criteria it can handle, it requires Alteryx configuration changes like Join fields in order to configure it for different situations and can cause data problems if not configured correctly.

Here are my 2 main ideas at this point.

Backup Plan

The Excel Controlled "If Statement" Approach.

This approach is basically to keep the complexity on the Excel side, and then to pass finished formulas over to Alteryx as text which would be applied via the Formula Tool... On the "Filter" side of the If Statement Builder, there would be drop down controllers to select Field Names and Values and Logic Controls like (AND, OR, >,<,<>,=,CONTAINS). On the "Changes" side of the If Statement Builder, there would be drop down controls to select the Field Names and New Field Values that will be used when the criteria are met.

The massive downsides of this is that will require an extremely complex formula construction on the excel side, it totally underutilizes Alteryx and limits future improvements, and I have never seen complex If Statement excel formulas containing Field Names used in Alteryx via text...

Primary Plan

The Iterative Tiered Filter / Formula Approach

This approach is essentially to build a more simple UI control tool on the Excel Side and leave all of the hard work for Alteryx. The dropdowns would function essentially the same as the above approach, but the layout would be a much more friendly. On the Filter control side - it would have Drop Downs for Field Names, Logic Controls like (AND, OR, >,<,<>,=,CONTAINS), and Values for that iteration. Then on the Formula side - it would have Dropdowns for Field Names and Values for that iteration. Alteryx would then have a multi-tiered Filter approach running in series (each handling a different Logic Control configuration) until the pool of contracts meet all complex Criteria. Then the Formula Tool would apply the designated changes for that iteration. At the end of each run the rest of the contracts that did not meet the criteria would be Joined back with the changed contracts before the next iteration.

The downside is that this is a quite complex iterative macro on the Alteryx side that requires a lot of Filter and Formula tools to be controlled by parameters passed from Excel and synched by iterations. I am proficient in Alteryx on the whole, but I am still slow and have to do a lot of trial and error when it comes to iterative macro construction.

The massive upside to this approach is that I can expand this in the future to do things like fuzzy match, flag changes for auto change / user approval, and even begin to push it toward the "AI" side of factoring in historical flag / approval data.

Any ideas or recommendations before I start putting a ton of hours into this?

[clmc9601]

Hi @iCFO,

Generally, the words "complex if statement" stand out as a warning flag to me. My experience in Alteryx, while rather limited, has led me to believe that there is almost always a better way than writing a single, complex IF statement. I'd recommend splitting up the work among different tools and different formula expressions as much as possible. If you have an IF statement with multiple elseifs, split it up logically among different expressions. I say this because a) it's easier to troubleshoot and explain what's happening, b) easier to correctly configure the connected interface/action tools, and c) easier to pause in the middle of building and return later.

As far as other recommendations to make troubleshooting easier, start small! Build a small piece, and make sure it works. Then add on to it. This is especially helpful with macros since sometimes they feel like a black box. You can include extra macro output tools along your inner macro workflow to "sample" your records and see how the macro is affecting them among iterations. Also, start out with the field names you want long-term. It gets more difficult to change them after you start building a complicated workflow! 

If you already know what values you want per iteration (for example, if you're changing filter field, operator, and operand and know them already), it might be more efficient to do a batch macro. Just include all of these in a text input tool and connect the control parameter to them. It makes it easier if you want to have a predetermined set of options per batch, but they do have to be changed within the text input tool unless you set up a ton of interface tools.

I hope this helps!

[iCFO]

Thanks for the reply.

The complex "if statement" is definitely just my last ditch backup plan. While my field names are set in stone, that doesn't reduce the complexity of this project much. The macro must be dynamic from the standpoint that any combination of field names / values / logic operators can be used to define the match criteria.

You are right about this Filter / Formula approach being a Batch Macro instead of Iterative.

I will try to get a single Filter / Formula batch macro to work, and attach it if (or when...) I hit a wall.

Thanks.

[iCFO]

I kind of surprised myself a bit on this one, but I was able to pull it off version1 pretty quickly. I used a batch macro for formula evaluation nested within another batch macro. It even detects and self adjusts formula construction to match data types. Now I just need to build a nice user friendly Excel controller for it.