Detours, Empty Fields, and Rankings

[RithiS]

I created a ranking macro based on this community post, which can be downloaded from the Alteryx Analytics Gallery. Who doesn’t want to know what’s the best and worst within a dataset?  It looked like a fun project for my first Alteryx macro. Before I dive into the macro I would like to share a couple concepts that helped me. Feel free to open the macro and see how these concepts are applied.

The Detour and Detour End tools

For all you Excel users, the Detour tool can be perceived like an IF statement. By default the Detour tool sends data through its left (L) output, but if a certain condition is met, the workflow will send data through the right (R) output. The condition can be an expression, a selection, or a checked box. Here is a simple example:

How this works:

• By default, this workflow runs the Formula tool because it is after the left (L) output.
• If the user checks the box, the Detour tool will run the Summarize tool after the right (R) output instead.
• The Detour End tool tells the workflow to unify the data process into a single stream for any downstream tools.

In the Ranking macro, I utilize Detours to enable sections based on the selected Ranking types. If the user only wants Standard rankings, the section to generate Standard will run while the other sections skip to the end. The way the macro is built allows users to update or add a section without breaking another.

Include [None] in Interface Tools

Some Interface tools like the List Box and Drop Down can generate list values from connected tools. In the List Values section of a List Box or Drop Down tool, select Fields from Connected Tool. There is a check box that states Include [None]. If [None] is selected by the user, the workflow treats the field selection as empty.

In the Ranking macro, [None] is the default selection for the grouping and second ranking fields. The macro updates the direction of Detour tools based on these selections. For example, one of the Action tools inside the macro is configured as so:

• In the Select an action type drop down box, the Update Value with Formula is selected
• Under Value or Attribute to Update, the selected value is @value - value=’False’
• In the Formula box is the expression !IsEmpty([#1])

How this works:

• The expression IsEmpty([#1]) returns ‘True’ if the selection from Connection [#1] is empty.
• The ! in front of it makes it return the opposite, so if the Connection [#1] choice is empty then !IsEmpty([#1]) will return ‘False’.

The Action tool is updating the DetourRight attribute of the Detour tool, so the DetourRight attribute should be ‘False’ when it’s empty and ‘True’ when it’s not empty. If DetourRight is ‘False’, then the Formula tool will run whereas if it’s ‘True’ then the Summarize tool will run.

These two concepts are easy to understand but can be very helpful. Other concepts used in the macro that I didn’t mention include the Dynamic Select tool, the Dynamic Rename tool, and utilizing Constants. These are great tools to begin learning.

Ranking Definitions

Now I’ll get into using the macro and the rankings. This macro ranks data on the selected field. Different ranking types handle ties and proceeding numbers accordingly. There are five types of rankings to choose from:

• Ordinal – Each item gets a distinct ranking, even if they are equal. If items are equal, the ranking can be assigned randomly or arbitrarily. There is an option to add a second ranking field for arbitrary tie-breaking.

• Standard – Items that are equal get the same ranking, which is the minimum of their ordinal ranking numbers. Gaps may exist. The assigned ranking is 1 plus number of items ranked before it.

• Dense – Equal items are assigned the same ranking. There are no gaps, so the next assigned ranking number immediately follows the preceding number.

• Fractional – Equal items are assigned the same ranking. An item’s ranking is 1 plus the number of items before it. If items are equal, the ranking is the average of their ordinal rankings. It’s fractional because a ranking can end with 0.5.

• Modified Competition – Equal items are assigned the same ranking, which is the max of all their ordinal rankings. The gaps are the number of equal items minus 1.

 

Additional information can be found on the Wikipedia page for Ranking.

Configuring the Ranking Macro

One or more ranking types may be selected to output in its own column.

 

Only a ranking field needs to be selected. The macro will rank the entire dataset on the ranking field.

By default, [None] is selected for the grouping field. Users can select a field to group assigned rankings. The assigned rankings will reset to 1 for each new group.

Ordinal rankings may require an additional field for ranking, so there is an option to rank on another field.

This shows TotalOrders sorted after UnitsSold. The ordinal ranking uses TotalOrders as an arbitrary tiebreaker.

Once again, you can download the macro from the Alteryx Analytics Gallery. I have also attached a yxzp file with fictional data. Please give the macro a try and let me know what you think!

RankingMacro_Example.yxzp