Creating user controlled groupings in a Weighted Median Alteryx Macro

There was an interesting question posted on the Alteryx community about which macro was best for calculating a weighted median. Adam Riley has a macro inside the CReW macros which does this, but wanted to see if it was possible to create one allowing dynamic selection of the grouping (like in a Multi Row Formula). I thought I would document the process I went through.

All of the examples have been created in v10.5. Everything should work fine in older versions as well, but you will need to edit the yxmd or yxmc file and replace the version number at the top (use 10.0 for both v10 and v10.1).

Initial Computation

image

The calculation itself is fairly straight forward. Taking the Wikipedia page’s example as a starting point.

    1. First Normalise the Weights so they total 1
    2. Sort into ascending Value order
    3. Calculate the Running Total Weight
    4. Filter to those greater than or equal to 0.5
    5. Select the first row and you will have the Weighted Median

This flow can be download here.

Grouped Calculation

The next step was to change it so it computes the weighed median with a grouping. Stealing Adam Riley’s macro sample data to start with. A few fairly straight forward changes and then we are done.

  • Add the grouping to the summarise tool
    image
  • Replace the Append Fields tool with a Join Tool and join on the Group
  • Adjust the grouping in both the Multi Row Formula tool and the Sample Tool
    image

The grouped version can be downloaded here.

Converting To A MacroWeighted Median

Alteryx makes this very simple. Just right click on the text input and select Convert to Macro. Select the macro Input and then specify you want it to have a Field Map. (As a side when making this I noticed the Optional Incoming Connection box – this was added in 10.5 and is brilliant!).

image image

The field map causes the input to have columns renamed. Fortunately Alteryx makes it east to reverse this process. Drag the magnifying glass output to the lightning box on the Select tool and it will automatically add an Action tool reversing the Field Map.

image image

After that need to replace the Browse tool with a Macro Output tool. Finally a little bit of reconfiguring to specify that the output depends on the input and add an Icon.

image

This version of the macro can be downloaded from here.

Adding Dynamic Grouping

So far this has been fairly straight forward. The next step is to make it so we can select different sets and levels of grouping. Alteryx has an interface tool just for this – the List Box. I added a ListBox and connected it’s Question Input to the Macro Input tools Question output. I then specified I wanted it to generate a Custom List with a separator I expect to be unique (§).

image

If the user chose to select nothing then we would have a problem with the Join tool, which needs a join! In order to avoid this I added a formula tool returning a constant of 1 which can be used in all the groupings to avoid this issue.

As I am not returning either the Weight or the Value I removed the Reverse Field Map as expected this was going to just add complexity to the macro.

This is where it gets a lot more fiddly as we are going to be rewriting the Xml of tools (and also a complete pain to test).

Summarize Tool

The next step is to connect the ListBox to the Summarize tool.  Connecting the ListBox Question output to the Summarize tool’s action input automatically adds an Action tool in Update Value mode. Unfortunately this won’t be able to update the summarise tool without a degree of fiddling.

Lets take a look at the Xml config the summarise tool:

<Configuration>
<SummarizeFields>

    <SummarizeField field=“Constant” action=“GroupBy” rename=“Constant” />
    <SummarizeField field=“Group” action=“GroupBy” rename=“Group” />
<SummarizeField field=“Weight” action=“Sum” rename=“Sum_Weight” />
</SummarizeFields>
</Configuration>

The action tool is going to need to rewrite the section inside the SummarizeFields Node and replace it with some dynamic Xml. We know we are going to want to keep the Sum_Weight and the GroupBy Constant but are going to have generate a set of GroupBy entries depending on the values selected. So first we change the Action tool to target the correct Xml node and tell it to update the Inner Xml based on a formula.

image

The formula is the next problem. We have a list of fields with a separator coming out of the ListBox. A little experimenting with the Xml showed me the rename attribute is optional for a SummarizeField. That means we can do some substitution on the separator and produce a valid Xml string for the configuration.

  • We always want to have: <SummarizeField field=“Weight” action=“Sum” rename=“Sum_Weight” />
  • We also always want to have <SummarizeField field=“Constant” action=“GroupBy” rename=“Constant” />
  • If no grouping fields are selected we don’t want to add anything else.
  • Otherwise for every entry in the list box we need <SummarizeField field=FieldName action=“GroupBy” />
    First we add a prefix: <SummarizeField field=
    We can do this by replacing the separator with: action=“GroupBy” /><SummarizeField field=
    We then finally add a suffix: action=“GroupBy” />

The formula we need is:

‘<SummarizeField field=”Constant” action=”GroupBy” rename=”Constant” />’
+ IIF([#1]!=””, ‘<SummarizeField field=”‘ +
Replace([#1], ‘§’, ‘” action=”GroupBy” /><SummarizeField field=”‘)+
‘” action=”GroupBy” />’, “”)
+ ‘<SummarizeField field=”Weight” action=”Sum” rename=”Sum_Weight” />’

Multi Row Formula Tool and Sample Tool

Both of these tools have a similar section of Xml to work on GroupFields in the Sample tool and GroupByFields in the Multi Row Formula Tool. Both of them have a set of Field nodes within them, slightly annoyingly it has a name attribute in the Sample tool and field attribute in the Multi Row Formula Tool. The sample below is from the Sample tool:

<Configuration>
<GroupFields orderChanged=“False”>
<Field name=“Constant” />
<Field name=“Group” />

</GroupFields>
<Mode>First</Mode>
<N>1</N>
</Configuration>

The process is very similar to the SummarizeField above (for the Multi Row Formula you need to replace name with field):

  • We also always want to have <Field name=“Constant” />
  • If no grouping fields are selected we don’t want to add anything else.
  • Otherwise for every entry in the list box we need <Field name=FieldName />
    First we add a prefix: <Field name=
    We can do this by replacing the separator with: /><Field name=
    We then finally add a suffix: />

The formula is hence:

‘<Field name=”Constant” />’ + IIF([#1]!=””, ‘<Field name=”‘ + Replace([#1], ‘§’, ‘” /><Field name=”‘)+ ‘” />’, “”)

Join Tool

Saving the most complicated to last. The Join tool’s Xml looks like:

<Configuration joinByRecordPos=“False”>
<JoinInfo connection=“Left”>
<Field field=“Constant” />
<Field field=“Group” />
</JoinInfo>
  <JoinInfo connection=“Right”>
<Field field=“Constant” />
<Field field=“Group” />
</JoinInfo>
  <SelectConfiguration>
<Configuration outputConnection=“Join”>
<OrderChanged value=“False” />
<SelectFields>
<SelectField field=“Right_Constant” selected=“False” rename=“Right_Constant” />
<SelectField field=“Right_Group” selected=“False” rename=“Right_Group” />
<SelectField field=“*Unknown” selected=“True” />
</SelectFields>
</Configuration>
</SelectConfiguration>
</Configuration>

The JoinInfo nodes are both the same as the Multi Row Formula Tool. So we can just copy the action tools for it and target the JoinInfo nodes. This leaves the section in the SelectFields to do. This is quite similar to the SummarizeFields case above. The rules this time are:

  • We always want to have: <SelectField field=“*Unknown” selected=“True” />
  • We also always want to have <SelectField field=“Right_Constant” selected=“False” rename=“Right_Constant” />
  • If no grouping fields are selected we don’t want to add anything else.
  • Otherwise for every entry in the list box we need <SelectField field=“Right_FieldName selected=“False” />
    First we add a prefix: <SelectField field=
    We can do this by replacing the separator with: selected=“True” /><SelectField field=
    We then finally add a suffix: selected=“True” />

The formula for this final case is:

‘<SelectField field=”Right_Constant” selected=”False” rename=”Right_Constant” />’
+ IIF([#1]!=””,
‘<SelectField field=”Right_’ +
Replace([#1], ‘§’, ‘” selected=”False” /><SelectField field=”Right_’)+
‘” selected=”False” />’,
“”)
+ ‘<SelectField field=”*Unknown” selected=”True” />’

End Result

The final macro can be downloaded here. There is a simple test workflow here. The User sees:

image

and the end result is a table containing the specified grouping and the calculated Median values:

image

5 thoughts on “Creating user controlled groupings in a Weighted Median Alteryx Macro

  1. Hi James,

    After recently building a weighted median calculation for Tableau I was thinking about doing an Alteryx version, thanks for doing this!

    I have a question for you – how do you validate the XML output of an Update Raw XML with Formula action (i.e. see the XML that your calculation is generating)? I don’t know any way to see the XML output when I do a test run of the macro, so I’ve been resorting to a) adding a Message tool and wiring it up then b) using the same formula as I’m using in the XML tool to generate the text for the message then c) putting the macro inside a test workflow then finally d) running that test workflow and looking at the results. I’m hoping you might know an easier method?

    Thanks,

    Jonathan

    Like

    • Hi Jonathan,

      Unfortunately it’s a nightmare. That’s pretty much the way I did it.

      While experimenting I tended to use the formula tool to create the value then copy to xml of a summarise tool and see what happened.

      I attempted to create a logvalue function in the c++ sdk but not possible – have grovelled at Ned to get such a function for debugging.

      Sorry no magic bullet
      James

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s