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 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: * 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)? Dynamic Transpose.yxwz action_tool_error.png xml_tool.png

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

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)?

Dynamic Transpose.yxwz

action_tool_error.png

xml_tool.png

Macros

Batch Macro

Accepted answers

KimLamNg

Hi @vsreis You can configure the action tool inside a batch macro instead. This approach was posted by danilang in this post: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Highlight-cells-that-No-across-whole-workbook/td-p/643692

I actually applied his approach for my specific use cases and it worked like a charm . For me, the tricky part was to formulate the right xml to replace the standard one of the main tool (Transpose in your case).

All comments

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.

Dynamic Transpose.yxzp

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.

Gaurav_Dhama_

That should be possible, can you give a dummy data and also some logic that you are trying to work with (sample workflow would be best)

vsreis

@Gaurav_Dhama_ yes the example flow I shared represents exactly what I am trying to do.

The problem is that, from the Input Data, I need to dynamically transpose some columns. In other words, instead of manually selecting fields in Key Columns and Data Columns in the Transpose configuration, I'm looking for a solution that defines these selections based on logic automatically, adapting to the fields in the input dataset.

apathetichell

@vsreis follow my instructions

Gaurav_Dhama_

@vsreis i will give it a try once i am back on my laptop, btw did that xml update macro i posted help solve your problem?

Gaurav_Dhama_

I gave it a try, and it is working as i want it to work. Check out the attached workflow.

Note that, i am making Key columns dynamic as well, that is why the workflow is quite big.

Go through the workflow, make any changes in container "Identifying the key and value columns" only, rest should ideally work automatically.

Let me know if you have any questions.

@apathetichell let me know your thoughts on it and also if you can think I could have done better.

Dynamic Transpose V2.yxmd

CoG

Here is an approach that @apathetichell described. This is certainly one of the best ways (if not the best) to tackle this problem because of the simplicity of the Transpose Tool. The "Key" columns serve no function at all other than appearing in the output. This limits the dynamic requirement. As long as you have logic for what fields should be listed as key's and which are values, you can partition your data into two separate sections, associated via RecordID, that you can normally transpose and then rejoin to establish the "Dynamics" you are looking for since the Transpose Tool has the built in option for "Dynamic or Unknown Columns" in the data columns section. The Action Tool wasn't working because without Interface Tools, it is almost entirely useless (you cannot reference fields from your dataset the way you tried to do, since all connections in Alteryx are one-way gated).

Hope this helps and Happy Solving!

Sample Workflow.yxmd

apathetichell

@CoG @Gaurav_Dhama_ --- the trick for dynamic keys is ---> interface tool -> dynamic rename --- if [_CurrentField_]='this is my primary key' then 'KEYCOLUMNFORTRANSPOSE' ELSE [_CurrentField_] endif ---> the action tool replaces the specific this is my primary key ---with the fieldname --- and KEYCOLUMNFORTRANSPOSE is set up in the transpose tool. after transposing --- or whatever, a second dynamic rename/action tool combo changes this back to the original value using inverse logic.

@Gaurav_Dhama_ I'd love to see your workflow --- but I don't have an Alteyrx license --- so is this my sole source for vicarious Alteryx workflow building --- if you are building an xml version --- can you test it with fields with the following names:

field1&field2

dates < july 5th

dates > july 6th.

does your workflow still work if those are the field names --- and those are the fields you are selecting and running through your xml?

DawnDuong

Hi @vsreis

From a UX point of view, is the objective:

1) No user input is required, the key fields and data fields should be selected purely based on the anatomy of the field names (e.g. contains "price");

2) User input is required to specify the rules? e.g. contain certain words or is of certain structure.

if (1) then very limited level of "dynamic update" is required, and this can be easily handled by the Dynamic Select Tool such as the solution posted by @CoG .

if (2) then the more straightforward approach, which is also easier to debug and review, is to use a combination of interface tools (radio button, text box, list box etc...), depending on the specific UX requirements. I attached here a really simple example where you can let user select which fields to be key fields.

Cheers,

Dawn.

Simple UX.yxzp

KimLamNg

apathetichell

@vsreis accept a solution here ---> we've put in enough work as a community to explain how to solve your problem. It's time for you to work on this --- get something that does what you need - and accept a solution or 2 or 5.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335