Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamically Changing Text Multiple Times Within Same String

KieranRidge
7 - Meteor

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

13 REPLIES 13
kelsey_kincaid
12 - Quasar

Hi @KieranRidge There are lots of ways to solve for this, but one way would be to parse out the word after the word "field=" into its own column, then use the Replace() function to replace "Dimension". I've attached an example!

KieranRidge
7 - Meteor

Thanks so much, Kayers, but I'm looking for a way to do this in a single formula tool given that this is just one component of dynamically rewriting XML code within a macro :).

MichalM
Alteryx
Alteryx

@KieranRidge 

 

Could you please upload sample of the data feeding into the formula?

danilang
19 - Altair
19 - Altair

Hi @KieranRidge 

 

Here's your one-liner in a single formula tool.

 

REGEX_Replace([Field1], '(<SummarizeField field=")(.*.?)(" action="GroupBy" rename=")(.*?)(" />)', '$1$2$3$2$5')

 

(<SummarizeField field=")  first matched group not including the brackets.  Exact character match

(.*.?) 2nd group.  Non-greedy match of any character.  Basically anything between group 1 and group 3

(" action="GroupBy" rename=")  3rd matched group

(.*?) 4th group.  Non-greedy match of any character.  Basically anything between group 3 and group 5

(" />) 5th group.  Exact match

 

replace the original string with $1(Group 1), then $2, $3, $2 in the 4th position to duplicate the field data, and then $5.    

 

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

becomes 

<SummarizeField field="Campaign" action="GroupBy" rename="Campaign" />

 

 

Dan

 

 

KieranRidge
7 - Meteor

Dan, thanks so much - it's beautiful, but I should've specified I need all rows basically on one line in same record. So I would need it to apply on all three occurrences in the string below in one cell:

 

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

 

And it would need to change each instance of rename="Dimension" to the respective field in its bracket (Campaign, Site, Device Type)

 

And the text in this example doesn't change after using your formula. Please let me know if you'd be able to resolve the matter 🙂

MichalM
Alteryx
Alteryx

I think it will be much easier to use the Summarize tool once you replace the values and concatenate the individual lines. This will only add one more step into your process.

danilang
19 - Altair
19 - Altair

Hi @KieranRidge 

 

Is the goal of this process to parse Alteryx xml files, .yxmd, etc?  If so, you'll save yourself a lot of grief in the long run, by importing the file and splitting by new line, thereby getting each line in it's own record.  Add a record ID using a multi-row tool incrementing by 100 between each line and you'll be able to edit, add, and delete lines as needed.  

 

You'll be able to filter on line type, i.e. StartsWith("<SummarizeField field="), make bulk changes that apply only to this type, union the rows back and sort by RecordID to get your lines back in order

 

Dan 

 

KieranRidge
7 - Meteor

Hello, my ultimate goal is to dynamically update the XML code for Summarize tool in a macro. So this would be the before:

 

<SummarizeField field="Dimension" action="GroupBy" rename="Dimension" />

 

and this would be the after:

 

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

 

 

Right now, in the attached workflow, I only got as far as this below, where the rename portion of each bracket is still Dimension.

 

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

 

I've attached the formula I used to get that far, but would like to change the rename part to match the respective field within each bracket.

 

(I currently have this outcome in my macro in a few extra steps, but the goal is to do it all in one Action tool regarding the Summarize tool.)

 

Workflow is attached - please let me know if anyone would be able to update the formula to achieve this final step.

 

Thanks so much,

Kieran

 

 

 

 

 

MichalM
Alteryx
Alteryx
When I've build a dynamic summarize macro before I built this portion of the configuration outside of the main macro and fed it in via a control parameter. I then used the update xml option within the action tool to replace the relevant bit of the summarize configuration.
Labels