Dynamic Replace Macro Help

[smoskowitz]

Good Day All -- 

I am working on a project where I am trying to Dynamically replace header names for various templates provided to me. I thought that the Dynamic Replace tool was the answer, but what I learned is that once it looks at a column, it will not look at that column again.

I then found this article and thought, not all hope is lost:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Replace-Tool-unmatched-fields-... created by @iCFO.

It appears to be the answer I am looking for. However, I cannot seem to get it to work. My main workflow is slightly different and builds out all formulas based on a word I want to search and replace with.

At this point, I have spent about 8 hours trying t figure out what I am doing wrong within the iterative macro but cannot seem to do so. If someone would be so kind to take look and see if you have any thoughts, I would be most appreciative.

Thanks,

Seth

[iCFO]

If I understand correctly, you are attempting to search for a certain word within a header text string and then replace just that one word? Such as a search for "Combined" in headers like "Combined Services" or "Combined Merchandise", and then a replace "Combined" with "Total" to get "Total Services" or "Total Merchandise"?

If this is the case, then I recommend doing the change on the excel side. You will need to store the find and replace values in some file like excel anyways, and you can easily do it via a formula and user friendly table that can be quickly customized for different projects. I do heavy automation on the excel side with the basic solution that you linked to above and it works great. It also has the added bonus of being user friendly for less technical users.

https://exceljet.net/formula/find-and-replace-multiple-values

I am not able to check out your workflow at this point, but I will try to take a look soon and get a better idea of your issue.

[MarqueeCrew · Accepted solution]

@smoskowitz ,

Seth,

Thanks for ZOOMing with me and showing me your challenge. Essentially there are 3 data sources that create similar content with varying data formats. Using the naked eye and human brain we identified a simple if-then way to find the format uniquely. 

1. Input data (first row is data

2. select records 1-11

3.Create template ID based on v the cases of values, else null

4. Summarize max template ID

 now we know which column names are appropriate to each field. 

We created a document with template I'd, f#, new field name.  We can use that data with all templates loaded by joining the contents to #4 above.   Now we only have valid replacement data. 

we use a dynamic replace to assign the field names.  With warnings off, we leave extra data in the form of F#.  Using a dynamic select we eliminate fields with that pattern of name.  

now we eliminate noise records.  We identified records with a field that has 6 digits as valid data and filtered the data on that pattern match.  

finally we added a message to stop the process if #4 template ID is nulll. 

there is more work to integrate and QC the process ahead. If column ordering or columns may be added or omitted, you need more defensive configuration.  For now, error those cases. 

cheers,

 mark

[smoskowitz]

Thank you again @MarqueeCrew for your assistance. Below is a screenshot of what we (and be we I mean he) came up with. I believe this will get us going with more work to be done.

Also, it was quite a pleasure to just chat as well!

Seth