Step Through the Multi-Verse

[patrick_digan]

If you’ve ever used the multi-row or multi-field formulas tool before, you know that these tools can do some pretty cool things. If not, you should definitely learn them first:

Multi-Field Interactive Lesson | Multi-Field Tool Mastery

Multi-Row Interactive Lesson | Multi-Row Tool Mastery

Once you know how these tools work, perhaps you’ll be thinking the same thing as me: Why can’t I use both of these tools at once? And that Row+-1 thing is pretty cool, what about the same concept for Column+-1? And what if I want to use the Multi-Field formula tool with dynamic field names (like Right_CurrentFieldName)?

Introducing the Multi-Row –Field –Column Formula tool. It’s not the most original of names, but don’t judge a tool by its name. This one powerful tool combines the multi-row and multi-field tools into 1! Plus, it adds the ability to reference columns similar to the multi-row functionality. Let’s check out a couple examples below. These examples come with the tool when you download it from the gallery.

Example 1: Multiple Multi-Row Formula tools use the Multi-Field syntax (that’s a mouthful)

Let’s suppose you have monthly data for your 10 accounts like so:

You’ve been tasked with multiplying these numbers together for each month. So January should be Account1 * Account2 *… * Account10 . Same thing for February-December. For a single month, you could use a multi-Row formula tool. With 12 months, 12 multi-row formulas tools would be a bit much. The “standard” approach to solving this in Alteryx would be the transpose-crosstab; however, let’s see if our new tool can help at all.

Here’s the configuration to solve a problem like this:

The Values for Rows that Don’t Exist  is applicable here since we’re referencing Row-1. I’ve selected Null for strings, 0 for numbers. This means that our first row will have a value of 0 for Row-1 calculations.

The Values for Columns that Don’t Exist isn’t applicable in this example.

The next box allows us to update the existing fields or create new ones. I’ve selected Update existing here which will overwrite our data.

Now we need to select which fields we apply our formula to. In trying to make this tool fit as many use cases as possible, I have 3 different options: Dynamic Field selection, Manual fields to include, and manual fields to exclude. Note that all fields are output from the tool, you’re just selecting which fields you want to apply the formula to.

Dynamic Field Selection: This box feeds directly into a dynamic select tool so you can select fields dynamically. For example, you may want to select all the fields with the word Field in them, you would type in the box: Contains([Name],”Field”). See the help docs for more info on the fields available for use in the dynamic select.

Manual Fields to Include: Check any of the fields that you want the formula expression to apply to.

Manual Fields to Exclude: Check any of the fields that you don’t want the formula expression to apply to. All other fields will have the formula expression apply. For example, if you want your formula to apply to all fields except a RecordID field, just check the RecordID field and the macro will grab all the other fields at runtime.

Here we’ll just exclude the account field.

We have the ability to group by, but we’ll keep our examples simple and not use that functionality.

We’ve saved the best for last with the expression window. Build your formula just like a formula tool using standard functions and variables. See the Expression Editor. Note that I’ve added row functionality, column functionality, and _CurrentField_ functionality. For those pieces, here is an explanation of how to build the syntax:

[Row+1:FieldName:Column+1]

Row+1: This is an optional parameter that allows you to specify previous (-) or next (+) rows. The number specifies the number of rows to move up (-) or down (+). This is the same as the multi row formula syntax. For example [Row+1:Field1] or [Row-2:TotalField]

FieldName This is the name of the field that you want to select. You can:

1. Specify a set field name - [Row+1:Field1:Column-1]
2. Use the currentfield syntax like the multi field formula tool - [Row-1:_CurrentField_:Column+1]
3. Use the currentfield syntax plus any string - [Row+2:Prefix_CurrentField_Suffix:Column-1]
   1. Prefix and Suffix are any letters or numbers before or after whatever the currentfield is. If you’ve selected Field1 and Field2, then [Left_CurrentField_Right] would be translated to LeftField1Right and LeftField2Right. This could also be combined with any row or column modifiers.

:Column+1 This is an optional parameter that allows you to specify previous (-) or next (+) columns. The number specifies the number of columns to move left (-) or right (+).  For example, [Field1:Column+1]. Note that it can also be combined with Row or currentfield functionality ([Row-1:_CurrentField_:Column+1] is valid).

In this case, here’s the formula I’ve written:

Max(1,[Row-1:_CurrentField_])*[_CurrentField_]

The max formula is really only applicable for the first row. When Row-1 is 0 because it doesn’t exist, max(0,1) will return 1. You could also had something like If Isempty([Row-1:_CurrentField_]) then 1 else [Row-1:_CurrentField_] endif. Assuming you’re not in the first row, you’re taking the value from the previous row and multiplying it by the value from the current field. By using the _CurrentField_ syntax, we’re doing this for all 12 months.

Going under the hood, let’s walk through a couple of the calculations. The first number calculated is January for Account1. The formula would translate to Max(1,0)*1.01=1.01. The second number calculated would be January for Account2 (Max(1,1.01)*1.25=1.2625), followed by January for Account3 (Max(1,1.2625)*1.21=1.527625) and so on down the January column. Then it would move 1 column to the right and start the calculations for February with Account1 -10, then March, etc.

Example 2: Multi-Column Formula tool

Let’s suppose you have the same monthly data for your accounts, but now you want to multiply across for each account and get Year to Date numbers for each month. So for account1, you want to multiply January by February to get February year to date. You would want this for all months. Let’s see how we’ve configured the tool this time:

The Values for Rows that don’t exist isn’t applicable since we don’t have any row modifiers in our expression. While the values for columns that don’t exist could come into play, we’ll select the fields such that it doesn’t matter.

We’ll again update the existing fields. For the fields, I’m using the manually select fields to include just to show a different option. Note that I’ve deselected Account AND January. For January, the Year to Date is the same as the month itself, so no need to calculate anything here.

Our expression for this example is:

[_CurrentField_:Column-1]*[_CurrentField_]

So for each selected month, we take the value in the field 1 to the left and multiply it by the value itself. The column specifier looks/works similar to the row specifier. While the row specifier comes before the field, I have the column specifier after the field.

Let’s look at the calculations going back to our original data. The first number calculated will be Account1 for February. That will be 1.01*1.22=1.2322. Then it will go DOWN to Account2 for February. That is 1.25*1.18=1.475. It will go all the way down February. Then it goes to March for Account1. It will take 1.2322*1=1.2322. Then it would go on down through all the accounts before moving to April etc. It’s important to remember that it starts with the left most selected field, works top to bottom, and then moves right to the next selected column.

Example 3: Dynamic Multi-Field Formula Tool

For our third example, let’s suppose that in addition to the data from examples 1 and 2, we also have a second data point for each account for each month:

Your boss has asked you to multiply the 2 data points together for each account for each month. While your immediate reaction may be transpose/crosstab, our new tool can help here too.

First we need to join the data based on account:

This gets all our data into a single row, and now we just need to multiply. For example, our output for January should be [January] * [Right_January]. Same thing for February through December. Our tool should be able to handle this pretty easy:

Notice this time that I’m creating a new field to show you how that works. First, we need to decide what datatype should the new field be? When creating the new field, you can select 1 of 3 options:

Keep Current Field Type: Whatever field the selected field is, the output will be the same.

Double: The output will always be a double.

V_String: The output will always be a string. For example, you may want to compare a numeric fields and save the result as a “true” or “false” flag.

Prefix/Suffix: This is similar to the multi-field tool. You can add any text to the beginning or end of your newly created field(s). For example, if you want a prefix of Calc_ to be added to your selected fields Field1 and Field2, your final output would be Calc_Field1 and Calc_Field2. In our example, I’ll just add _New to the end.

Also notice that in my field selection box, I’ve only selected January-December. I don’t need to also select Right_January-Right_December.

For our formula, we’re using: [_CurrentField_]*[Right__CurrentField_]. This will take the current field’s value, and then multiply that by the value of the field called Right_[yourFieldsName]. For example, the first calculation will be January_New for account1. It will be January * Right_January = 1.01*1838=1856.38 It would then do all the other accounts for January, before moving on to February, etc.

How does this tool actually work behind the scenes?

It’s a little complicated to handle the various situations, but at the very end there is a multi-row formula tool. I’m handling all the multi-field formula aspects in my macro via various mechanisms, and then I feed everything into a multi-row formula tool, one for each field selected. Hence, this is why it processes the data one field at a time, top to bottom.

Download the tool and try it for yourself! I’m curious what use cases people will have for a tool like this!