Using Data Filters in Altair Monarch

Altair Monarch is a leading data preparation solution and offers a collection of filtering techniques that can dramatically simplify and enhance the data preparation process. This blog post explores all the filtering techniques available in Altair Monarch, providing insights into their applications and benefits.

Understanding Data Filtering in Altair Monarch

Data filtering in Altair Monarch is about selectively narrowing down the data based on specific criteria. This process helps in focusing on information that is relevant to the analysis task at hand. Whether you are dealing with financial records, customer data, or operational metrics, Monarch provides an intuitive and powerful way to manage and manipulate your data.

For this post, we will show examples using a generic dataset that would be Marketing information from a banking institution and using Altair Monarch Data Prep Studio. The original dataset shows 4,521 entries.

Fig 1: Original Dataset before Filters

With Monarch there are three filter types to choose from: Formula-Based, Value-Based and Compound Based.

• Formula-Based: This option allows users to create custom filters using a formula editor. This type of filter is particularly useful when standard filtering options are insufficient for specific data analysis needs. Users can write expressions that combine multiple fields, functions, and operators to define complex conditions.
• Value-Based: These filters allow users to select specific values from a list or range to include or exclude from the dataset.
• Compound: involve combining multiple individual filters using logical operators such as AND, OR, and NOT. This type of filter is used to apply several filtering criteria at once to narrow down data more effectively.

Users would access these filter options under the Analyze Tab > Filter: > (Fig 2)

Fig 2: Filter location in Altair Monarch Data Prep Studio

Setting up a Formula-Based Filter

Let’s look at setting up a Formula-Based filter first. Once I click that option from the dropdown, I get a menu that shows me Fields (Columns), Functions and Operators (Fig 3).

Fig 3: Formula-Based Filter Menu

The Functions options are many, to find a list of each function and there definition you can go here.

For this example, let’s assume I have a program that I want to market only to banking customers that have an outstanding loan balance. To narrow down my list, I would need to add the formula in the expression field. I can either manually enter the formula if I know the syntax, or I can double click the options from the menus.

For this example, I chose “ZeroifNull” as my function, then I wanted it to search the balance column, to that was selected in the (), then lastly my operator is Greater than. So, this formula is saying:

1. Look at the Balance column.
2. If the value is Null convert it to Zero.
3. Then only return items that are greater than that number.

Fig 4: Formula-Based Filter Menu

The advanced tab on this menu allows you to change a few additional settings like ignoring duplicate entries, selecting specific rows to perform your filter, limiting the number of returned entries and a few others.

Setting up a Value-Based Filter

The Value-Based filter is much more straightforward. This allows you to filter your selection by using a specific value in any of your fields. So, using my same Banking Marketing Dataset, I will look to narrow down my entries to users whose marital status equals “Married”

In my menu, after selecting “Value” from the Filter, I will first look to change my field to the column I want it to search for my value, then in the Value box, I will type out Married. Users can also use Wildcard characters if their data entries may not be consistent. For example, I could also use M* to capture any entries that start with the letter M.

On the right-hand side, you will see the options to either include or exclude entries that match your value (Fig 5).

Fig 5: Value-Based Menu

The Advanced menu for Value-Based filters just lets users choose if they want to impose a limit on the number of returned entries.

Setting up a Compound Filter

Now that I have set up multiple filters “Has Balance” and “Marital Status”, I can now use a compound filter to find entries that meet the criteria for both of those previous filters or I can also return entries that meet one or the other of these filter criterion.

My Compound Filter menu has an expression box that was similar to what we saw with the Formula-Based filter. Again, if you know the syntax you can manually type out the compound in this box, or you can add/remove filters from the menus above. For this example, I want to return entries that have a balance on their loan AND are married (Fig 6).

Fig 6: Compound-Filter Menu

You can see from Fig 6, under the added filters section there is an option to choose:

• “all of the component” filters which would mean both filter criterion need to be met in order to return the entry, and changes the expression box to AND in between filter selections.
• “any one of the component filters” which would mean at least one of the filter criterion has been met.

Similar to the Value-based filter, the Advanced tab allows users to limit the number of returned entries.

Once the Compound Filter is added, we get are left with all of the entries that are both married and have an outstanding loan balance. Entries went from 4,521 to 2,572 as seen below in Fig 7.

Fig 7: Filtered dataset

All created filters can be saved and used for future data manipulation needs which makes for a quicker workflow, especially for those that reuse various data criteria constantly throughout their process. Filters are just one of the many simple, yet powerful features for preparing data using Altair Monarch. To learn more you can visit our product page for Altair Monarch or contact us directly.