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

[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.

[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!

[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.

[KimLamNg · Accepted solution]

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...

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

[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.