Dynamically Changing Text Multiple Times Within Same String

[KieranRidge]

Hi all,

I usually try to figure these things out on my own, but I'm a little stumped on this one.

I'm looking to change the string below:

<SummarizeField field="Campaign" action="GroupBy" rename="Dimension" />
<SummarizeField field="Site" action="GroupBy" rename="Dimension" />
<SummarizeField field="Device Type" action="GroupBy" rename="Dimension" />

to

<SummarizeField field="Campaign" action="GroupBy" rename="Campaign" />
<SummarizeField field="Site" action="GroupBy" rename="Site" />
<SummarizeField field="Device Type" action="GroupBy" rename="Device Type" />

Basically, on each line, the rename portion will be changed from Dimension to its respective line's field portion (Campaign on first line, etc.).

On each line, I'd have to replace the string Dimension to whatever the text is in between 'field="' and the following double quotation mark after the next string, so that the first line would replace Dimension with Campaign, etc.

This possibly reguires some Regex and searching for particular strings to find which text within the greater text we are using to replace. I'm looking to solve this via a basic Formula tool because this is only one component of a greater task of dynamically rewriting XML code in the Summarize portion of a macro.

Looking forward to being educated by the brilliant people who reply to this.

Thanks,

Kieran

[KieranRidge]

Thanks, Michal - the macro I'm working within uses the control parameter across multiple tools like Joins and Transposes where I'm using the same dimensions selected up front, so I'm not looking to solve this particular issue by building a dynamically updated Summarize tool outside the macro. Thus, I'm looking to solve this issue within one Formula tool (plus, if I solve this issue, it will help me apply similar logic of updating every occurrence within the same string on other issues). Thanks :).

[jdunkerley79 · Accepted solution]

Ok so REGEX is your friend for this one:

REGEX_Replace([#1], "([^+]+)\+?", '<SummarizeField field="$1" action="GroupBy" rename="$1" />\r\n')

This will match each variable in you '+' separated list into the $1 variable. It can then construct the XML you need.

The result for your test is:

<SummarizeField field="Campaign" action="GroupBy" rename="Campaign" />
<SummarizeField field="Site" action="GroupBy" rename="Site" />
<SummarizeField field="Device Type" action="GroupBy" rename="Device Type" />

[KieranRidge]

James, as always, this is absolutely amazing - thanks so much. Let me know if you have any links on Regex tutorials as it is my goal to derive what you built so that I can understand every component and break down/rebuild accordingly.

[jdunkerley79]

Nothing too complex in this one:

([^+]+)\+?

This Regex just matches blocks up to each + or the end of the string.

These are then a marked group (the brackets) and get recorded as $1

This can then be repeated all over the replacement string.

I like https://regexr.com/ for playing with RegEx