Today was our second day of Alteryx training and one tool that stood out was the multi-row formula tool. It can perform complex clean-up tasks with a few simple actions.
A problem we faced today was separating the multiple share class headers from the latest price (TNA Latest) column. For the data to be readable in Tableau we needed the share class to be in a column of its own and feature on the rows of the funds it matched. Here’s a picture of the table before we used the multi-row formula tool:
As you can see the first column (TNA Latest) is a mixture of strings and numbers due to the share class names also appearing in the column. This format makes calculations for the entire column impossible and leaves null values across in the rows with the share class name.
To solve this problem, we can use the multi-row formula to create and populate a new column. It can be used to match share class with the correct fund. Below is a picture of the tool and the actions I used to complete the task.
First, I had to create a new row which I named ShareClass as can be seen below the create new field selection.
After creating a new column, I then used the function box at the bottom of the pane. This is where the power of the Multi Row Formula becomes apparent. By using a simple if statement I’m able to populate the newly created column with information from a chosen column. Here is the statement I used:
IF isnull([Fund]) THEN [TNA Latest month-end ($mil)] Else [Row-1:ShareClass] ENDIF
The first part of the statement is pretty straight forward as it looks for rows with empty fund columns. Once it finds a null it populates the new column with the ShareClass name from the TNA column.
The impressive part is the section after ‘ELSE’, where it will fill the blank columns using the information from the row above, in a column that has yet to be created! The Row-1 statement, available from the variables drop down in the box above, will automatically populate empty columns with the information from the row above. This is extremely powerful as it allows Alteryx to populate rows based on theoretical columns.
Here is a screenshot of the data with the newly created column.
The new column is at the end and fully populated, however, you can see there are still rows of null data as they were initially populated the ShareClass header only. Therefore, a basic filter is needed to remove any rows where the fund column is null. Once this has been applied you will have a data set readable by Tableau as shown below.