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

Dynamic Transpose Key Field + Summary Field

bsharbo
11 - Bolide

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!

 

16 REPLIES 16
jdunkerley79
ACE Emeritus
ACE Emeritus

Nope didn't think trying to be difficult - just a deep bit of alteryx to expain. The clearer I can make the better, and helping to give understanding is what the community is great for.

 

The way I tackled this problem was set up what I wanted first without a macro.

Save the workflow.

Open in your favourite decent text editor (I like VS Code or Atom) as Alteryx workflows are just Xml.

Find the node you want to control and hopefully it is easy to read. Normally is as Ned and his team do a great job.

 

Normally I would expect to be poking at the Inner Xml of a tool's configuration as changing the structure (i.e. OuterXml) would normally break the tool, or at least muck the configuration up. 

 

I found this one of the hardest area to get started in, but is also one of the most powerful features. I cheated and used some of the great @chris_love brain when I started.

bsharbo
11 - Bolide

yeah unforunately looking at the XML doesn't help me much.  I know what I want to alter, and I usually just look at the XML in the configuration editor of  AlterYX itself (like the picture attached).  

 

The problem is I can't seem to figure out what my actual output of the code I have applied is (even with the macro browse tool, so debugging isn't possible). I am giong to suggest this to alteryx as it seems like a pretty big hole to me. 

 

For example: I have attached a workflow where I simply want to change ONE key varible on a join... This isn't what I want to do, but even this simple example isn't working... But I have no idea why.. I've done the same thing i've always done but for whatever reason it isn't working.... (There should be one record in the left output and 3 in the middle join output, however I'm getting one record in the left output and three in the right output).

 

I'm not sure how i'm supposed to tell this without doing a debug....

 

How w ould you debug the attached tool without just "guessing" or "knowing" what is wrong with it?

 

 

 

 

bsharbo
11 - Bolide

hey Bunkerly!  I was able to fix this problem by simply closing and re-opening AlterYX.  It looks like for whatever reason if you change a macro the outer workflow that calls it wasn't updating the new macro definition (I've run into this a LOT actually... not sure how to get around this problem).

 

 

To that note: Do you know how to get around this problem? If you have an outer workflow open and you change a macro it calls, do you always just Quit out of that workflow and re-open it to get the system to work correctly?

 

 

Thanks again for your help!  It looks like debugging simply isn't possible in Alterxy from everything i've seen so I will just put a note in that an enhancement to this would be necessary in the future! :-)

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Knowing and guessing never works for long. So I make it as simple as I can come up with. 

 

FWIW I think your macro is working! The output order is L, R, M (not L, M, R as on a Join) and the data matches as expected.

 

Either way, here are my tips:

 

First (and this is what I love about the community) turn on the option I just found in User Settings => Advanced to show the Xml in the properties window. Then lets look at the Join tool:

joinXml.jpg

 

Then I would split it into two different actions (just for simplicity). One to update the xml of the JoinInfo Left and one to update the right. This is then looking much closer to the last case.

 

I would test the formula I want to use as a regular flow and make sure the Xml I was producing was as expected. I can then copy and paste that into the tool am trying to run (in a text editor!) and see what happens.

 

Hope that helps

 

 

 

 

bsharbo
11 - Bolide

Yep I've been using the XML properties window too and I love that tool. Helps a lot. You made a comment and it sounds like the entire crux of my problem...

 

 

I would test the formula I want to use as a regular flow and make sure the Xml I was producing was as expected... When you say this, do you mean the XML that you see in the editor we just talked about, or are you saying you can output the XML values of whatever was actually run...  Those are two very different things and one solves the problem while the other is just 'helpful'...

 

You seemed to just imply that you could run a macro, change one of the values and then simply "view" the outputting XML somehow... (I hope that's what you are saying).

 

If you are saying I should simply look at the default XML of the tool, and then run a macro that has the result of the dynamic XML and paste that result into the correct section I want to update the tool, that is actually exactly what I have been doing....

 

 

my basic steps are

#1) Look at the tool's XMl.

#2) Run my dynamic XMl and see what the output string looks ilke

In my example I am wanting to join on two columns, so my XML dynamic replace statement (the one you gave in an earlier post)! resolves too <field field="Loan Grade" /> <Field field="LoanNumber" /> 

 

#3) Replace the section of the XMl that I don't want with the results of the dynamic XML tool

In my example I replace  <Field field="LoanNumber" />   with <field field="Loan Grade" /> <Field field="LoanNumber" />   and it works perfectly

#4) Make sure it runs....

 

My problem still lies in Step #3.. I haven't been able to clearly figure out what update value translates to the section of the XML I want.... So I know 100% I want to update the <field> tags within the <joinInfo> tags of "left" and "right"... However I just guessed that this would be an Update Inner XML option.   While this works, I don't like the "guessing" part of that statement... 

 

You have been extremely helpful and I guess me just guessing at what section is 'inner' vs 'outer' xml is what I am left with, but at least the code is now working the way I want it too!  Thanks for putting up with my continual questions!

 

 

 

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Unfortunately I was meaning the later (as far as I know the former is not possible).

 

xmlMap.jpg

The Xml structure is reproduced as tree view within the Action Tool.

The InnerXml is the part of a node between the opening and closing tags (shown in yellow). The outer includes these tags (the black rectangles above).

 

In this case to control the join  we need to add a list of 'Field' nodes within the JoinInfo node. This can be done just by updating the InnerXml.

 

If you wanted to adjust the selected state of a column then you would probably want to use the outer xml of that specific column.

 

Hope that makes it a little clearer and yes it is a nightmare to debug.

TaraM
Alteryx Alumni (Retired)

@bsharbo wrote:

 

To that note: Do you know how to get around this problem? If you have an outer workflow open and you change a macro it calls, do you always just Quit out of that workflow and re-open it to get the system to work correctly?


 

When this happens for me, I save my macro, then go to the workflow that uses the macro, select the macro, cut (Ctrl x), paste (Ctrl v) and connect it back up. The cut/paste ensures the configurations are saved. I hope this helps - you should not have to close alteryx in between.

Tara McCoy
Labels