Dynamic Transpose Key Field + Summary Field

[bsharbo]

Hello everyone.  Does anyone know how to set up a macro that would read from a file (or dataset) and then take a list (dataset , comma delimited , etc..) of the key fields that the datashould should be transposed by and dynamically alter the Tranpose tool?

The problem is I have no idea how to use an action tool to update the transpose tool to select MULTIPLE key columns... 

If possible I would like this to be a macro so that it can be used in other workflows, instead of a stand alone application... but if that's not possible I will take a stand alone application as well :-)

I have included a very simple workflow that shows how I was able to do this with choosing One Key field using a text box input.... I would like to extend this problem to multiple key fields, and also make it so it is inside a macro and the users don't need to prompt on it! 

Let me nkow if my question was not clear and thank you!

[jdunkerley79]

Havent had time to build something fully for you.

But the attached package should show you using a batch macro and taking a comma separated list of fields to set the key fields on a Transpose Tool

Basically uses a formula to create the Inner Xml for the Transpose tool.

Hope it gives you a hint of how to proceed. Happy to help further

[bsharbo]

Hey jdunkerley79. Your process isn't actually working. In your end result everything is pivoted and the key columns aren't acting as keys unfortunately. 

I have a couple of questions related to your process that might answer why its' not working.

#1) You are replacing the UPDATE Inner XML with the following logic.

'<Field field="' + Replace([#1], ",", '" /><Field field="') + '" />'

When you say Update INNER XML, what section of the XML is updated? If I look at the XML version of your transponse in your macro (before your changes) it looks like this...

<Configuration>
<ErrorWarn>Warn</ErrorWarn>
<KeyFields>
<Field field="Some" />
</KeyFields>
<DataFields>
<Field field="Some" selected="False" />
<Field field="Data" selected="False" />
<Field field="*Unknown" selected="True" />
</DataFields>
</Configuration>

What you say update inner XML is it just updating the section between the KeyFields?  Or is it updating that + The sections betwen the DataFields tags?

Thanks!

[jdunkerley79]

At the moment it is just replacing the innerxml on the KeyFields. Will put together a better demo of what is possible this evening and post an update.

James

[bsharbo]

Thanks that will be extremely helpful!  I  guess I fundamentally don't understand how you nkow that it's only updating the data between the <keyfield> tags..... Do you just happen to know that the stuff between those tags is the inner XML and the information between the <Datafields> tags is the outer XML?

Is there a way to view the altered XML in some way, so that I could "run" the program and see what the XML changed too during the run?

As always your help is extremely appreciated :-)

[jdunkerley79]

Try this version, think I had mis clicked in the macro which was why didnt work

[jdunkerley79]

I think the old version I had misclicked. 

You can see what is targeted within the action tool by looking at the configuration:

Quite a subtle grey colouring!

[bsharbo]

Hey I appreciate your help! I am a little confused by your last answer. are you saying you can see something in the configuration that shows what you are updating? 

I honestly cannot see anything in the picture you just sent.... 

In general that was still extremely helpful as it got the correct answer, I am just trying to figure out how it worked so I can repeat the process on other tools.

For Example: I would now like to copy the same logic and now create a dynamic "join" tool  that will join based upon some key columns, and output only those columns as well... 

So to confirm: there isn't a way to view the 'completed xml' of the tool at run time?

Thanks!

[jdunkerley79]

Sorry what I was trying and failing to say was. Initial post had a bug in posterd workflow, which I fixed so was updating just the Xml within the KeyFields section. The image was showing a VERY subtle grey colouring around KeyFields. 

Nope sorry as far as I know the debugger window (inside Interface Designer) doesnt allow injection of control parameters.

However, take a look at http://community.alteryx.com/t5/Engine-Works-Blog/The-X-Ray-Browse-Macro-Debugging-Made-Easier/ba-p/...

It might help debug the process.

Hope it helps.

[bsharbo]

Hey jdunkerley79. As always thanks for your help! Please don't think i'm trying to be difficult, but am just trying to understand everything :-)

If you can't see the code execute, how did you personally know that the update the inner XML was the right way to go?  The tool doesn't seem to clearly note what the "innerXml" is, or what you are updating.

From the documentation I couldn't tell if I should be updating the inner or outer in this instance... and I can't really tell when loking for other tools... Is this just something alteryx assumes you know?  

I am going to look into that other post right away and thanks again!