How to configure Action Tool to dynamically update Transpose fields via XML?

[vsreis]

Hi everyone!

I'm trying to create a macro in Alteryx Designer that dynamically uses Transpose, meaning it changes Key Fields and Data Fields at runtime based on the fields in the input dataset.

What I already have:

• An Input Data that feeds a Field Info.

• A Filter that separates fields that start with "Price" (for DataFields) and others (for KeyFields).

• A Summarize that concatenates the column names with the <Field field="..." /> structure, generating a [Concat_Name] field.

• Next, I use a Formula Tool to generate the full XML in the [Full_XML] field, in the following format (Appendix xml_tool).

What I tried:

• Insert a Transpose Tool right after the formula (without manually marking anything).
• Connect an Action Tool to it, configured with:
• Update Outer XML
• Selected XML: <Transpose>
• Field to update: [Full_XML]
  
  Transpose (12): At least one Field must be selected
  Action (16): Parse Error at char(28): Unknown variable "#1"
  

Apparently, the XML is not being injected correctly by the Action Tool, and Transpose complains about not having marked fields.

What's the correct way to configure the Action Tool to replace Transpose XML based on the [Full_XML] field?

Does this approach actually work with Transpose, or do I need a different strategy (perhaps with nested macros, Batch, or an alternative)?

[apathetichell]

Don't do this. Instead use a dynamic select before the transpose. update the dynamic select to include the fields you want transposed. set the transpose to transpose dynamic fields.

[vsreis]

I appreciate your suggestion, but I don't understand how to implement it. Based on my example, could you apply your idea in a workflow?

[apathetichell]

ok --- so your workflow is something like:

input data, field info, filter, summarize, formula --- and then you are having an issue because you'd need another macro to feed whatever xml you have to your transpose tool via an action tool in another macro (it requires an inner/outer macro structure).

the inner/outer macro structure is easy --- getting your xml correct is a problem. It's hard --- you can get it to work and then something breaks and it's a huge headache.

the correct way:

input data --- dynamic select. transpose.

your dynamic select look for [Name] in ("Test") you action tool is configured from a listbox in select mode to generate a list in "field1","field2" mode. action tool replaces a specific string in dynamic select --- string is "Test" including the quotes. this provides the full list to look for values (ie fieldnames)--- your transpose thus becomes a dynamic transpose because it transposes whatever the dynamic select selects... Note --- if you have a key field you'll need to use an or statement in your dynamic select to capture that field and mark it as a key field in your transpose.

This is a pretty hard workflow --- and I think going the way you are going --- you've got a good deal of work in front of you.

[Gaurav_Dhama_]

Action tool cannot directly access the field that you create during the flow, so you need to enclose your transpose tool inside a macro that can use action tool to update the xml.

Now there are few more changes you need to make. We don't change the whole xml of a tool, but rather just outer or inner xml, in your case it is inner xml, so I got update your xml with the one that will work. I did not remove it but updated it in same formula tool so that you can see what was changed. Hope this helps.

[apathetichell]

@Gaurav_Dhama_ real talk --- how often do you find using an action tool in edit the xml mode (outer or inner) is the right choice?

[Gaurav_Dhama_]

Hi @apathetichell 
Honestly very rare, more like exceptional cases. So far i would have done that just few times, but those cases were out of projects with very rare requirements.

But it is still an interesting concept to be aware about, if a person is already in that direction, i would encourage him/her to understand it than leave it half way.

[apathetichell]

 I dunno --- my take is that @vsreis is still on the learning path for general macros -- -that they will never get there following that route. I'd stick to the dynamic select.

[Gaurav_Dhama_]

@apathetichell I do agree on that, if something can be done with basic tools, it should be done with those for sure. It's safer and more stable.

[vsreis]

The intention of this post is precisely to explore other possibilities beyond the approach I was already trying, seeking solutions that truly solve the problem dynamically. However, @apathetichell  apatia, your response seemed more concerned with defending your own idea than clearly explaining what you wanted to sugges.

I read your suggestion carefully, but I honestly couldn't understand how it would apply to my case. I even asked for a practical example, which unfortunately wasn't provided.

Even so, I remain completely open to understanding your proposal; if you could elaborate on it or demonstrate it with an example, it would be greatly appreciated.

In case I wasn't clear about the problem, I'll explain it again in a practical way:

When we use a selection tool (such as Select, Dynamic Select, or Transpose), we typically map multiple columns, for example, related to years: 2023, 2024, and 2025. Based on these columns, we create formulas or transformations. The problem arises the following year, when we have 2024, 2025, and 2026. What happens to the 2023 data? The tool will complain about the absence of this field, and the workflow breaks.

In my case, this happened with Transpose. And my question is: what would be the ideal way to make this process dynamic, avoiding this type of error?

I also believe that simple solutions are always the best; the problem is that, in this case, I haven't yet found one that is truly simple and efficient.