Recursive or Iterative Macro

[JTR616]

Hi Alteryx community, I'm trying to find a way to run an iterative macro that will go line by line through my dataset and compare to a set of criteria in another input table (3 columns of criteria). I also want it to start from the bottom of the criteria look up and work its way up.  If the condition is met then it should return tow columns from the look up table, leave null if no criteria is met. It may meet the criteria on the first line of testing, and it should retrieve the those values. Then if it meets the data a few steps higher then it should overwrite with the new columns.  Again working on a loop from last record to first on the criteria page. 

My criteria page is formatted with asterisks to indicate that any result unless specified is acceptable.  This process works in VBA but I'm struggling to find a way using any of Alteryx tools.   I have several processes that will need to replicate these steps but this should be an easy enough one to start with.  Would the proper way to do this be using the Python tool?  I'm happy to change the lookup tables format if that's the problem but from what I can tell Alteryx doesn't accept wildcards which seems insanely limiting.  Ideally not trying to use the write all the criteria into the formula tool, would love to be able to iterate through a rule table. 

I've tried several things and worked back with a designated person my account rep put me contact with but they weren't much help. 

Happy to answer any questions on the comments. Thanks for your help in advance. 

Edit will upload files 

[CoG]

I have a couple requests:

1. Can you upload an Exported Workflow (In the options menu) so that we have access to the "Macro Lookups" file?
2. This is a bigger ask, but a more simplified input and expected output would go a long way to ensuring that you receive the appropriate assistance. That being said, thank you for attaching a workflow!

Just reading through your request, it seems like you will not need the python tool. I'm curious if you need a macro as well, since it sounds like each row is operating independently of the rest (I'm curious if the Join Tool would be of any benefit, possibly Summarize Tool).

So is the output you are looking for just 2 columns from the Lookup table? I'm not quite sure what you mean by wildcards either. Some clarification would help tremendously. Thanks!

[Qiu]

@JTR616 
It seems that the input file is not included.

[JTR616]

I'll update the original post.  If you don't mind updating your post as well that would be appreciated.  My name and company don't really need to be called out.

[JTR616]

I've attached an updated updated file.  Thanks for telling me about the export option!  The reason I assumed I need a macro is i need it to iterate through each level of the second table from last record to first and and not escape escape after it finds the first match to the criteria.  It may find a match on criteria level 50 and it should retrieve those values, then if it it meets another set of criteria on line 40 it should inherit those columns as the higher they go up in the tables they take priority.  Its not a one to one match sadely so I didn't think a join would work. The lowest level of criteria is Sub-Department and after that entity and account would take priority. 

I'd love to upload a sample of the finished data but i've masked so much of the data my original vba macro wouldn't work anymore. :(

[Qiu · Accepted solution]

@JTR616 
It really took me sometime😁
I do agree that there is no wild card in the Join function.
So I will filter down the Join Condition as below.
As for looking from bottom to up, there is a good function as Sample by assigning Record number for both data stream then take the first record after Accending Sorting.

[PangHC]

I prefer to pressure to alteryx, instead of spilt them in complex.

for input data, I saw tons of duplicate, hence i use summarize to reduce the burden later.

for rules, I use multiple fields tool to convert it to fulfill regex syntax.

append all rule to Input data, only then filters with regex match. it clean and clear.

get the last rule and join back to raw data.

Note: cautions with character like ".\(){}", if have, do update in the multiple field tool. 

[JTR616]

So the data had many duplicates because I deleted most of the columns where the differences would have existed. The table represents transaction level data from our ERP.  Would this process still work if I skip the summarize?  Or should I keep the summarize and just group by all columns? 

[PangHC]

Hi @JTR616,

better to keep the summarize, (to avoid create duplicate category or records)

you just need to ensure all the columns is add back in the last join tool.