After creating a Filebox and uploading your files, these files must be transformed and stored in the database. 

The Data Mapper helps you translate and organize data to fit the database's structured format, offering various features, including advanced filtering options that allow you to exclude unnecessary line items without deleting them. Additionally, it allows you to perform data manipulations, such as filling blank rows with specific values.

Before learning how to use Data Mapper filters, it's important to understand the Data Mapper structure and how to create a new Data Mapper.

Use Cases & Benefits

Filter out Blanks
When working with a monthly financial file containing both transactions and beginning balance values,
the Date column for the beginning balance figures might be blank. You can use the 'Filter out Blanks' option on the Date column to exclude these entries from the database.

Fill Blanks
If your financial input includes an Account column that applies to all subsequent rows, you can use the 'Fill Blanks' filter on this Account column to populate the missing data. 

Advanced Filters
The Advanced Filter section provides a variety of filtering options for more precise data manipulation. Use the Advanced Filters or the 'Filter out/include only by Regex' options to include or exclude specific values easily.

Apply Data Mapper Filters

To access the filter editor screen in Data Mapper, you can follow these steps:

1. From the Database Preview: Click the filter icon next to the column name.
2. From the Header, Custom Column, or Dimension Section: Click the three dots ellipsis icon next to any field you've added, then select 'Advanced options.'

A blue outline on the filter icon indicates that an Advanced Options filter is in use.
Filters cannot be applied to Lookup columns, Table Attributes or Table IDs. To understand how to filter a Lookup column, review the Q&A.

Fill Blanks

1. Click the filter icon next to the column name in the database preview or the three dots ellipsis icon next to each field in the Header, Custom Column, or Dimension section, then choose Advanced options.

2. In the 'Advanced Options' window, check the 'Fill Blanks' box. Optionally, adjust the stopper option:

   'No Stopper' applies the value continuously.
   'Stop After' applies the value to a specified number of rows.
   'Stop When' skips applying the value after a specified condition.

3. Click 'Create'.
4. This applies the filter in the database preview, fills rows with data, and highlights the filter icon in blue.

Filter out Blanks

1. Click the filter icon next to the column name in the database preview or the three dots ellipsis icon next to each field in the Header, Custom Column, or Dimension section, then choose Advanced options.

2. In the 'Advanced Options' window, check the 'Filter out Blanks' box and click 'Create'.

3. This applies the filter in the database preview, removes rows with no data, and highlights the filter icon in blue.

Filter out by Regex

1. Click the filter icon next to the column name in the database preview or the three dots ellipsis icon next to each field in the Header, Custom Column, or Dimension section, then choose Advanced Options.

2. In the 'Advanced Options' window, check the 'Filter out by Regex' box and enter the relevant regex.

3. Click 'Create'.

4. This applies the filter in the database preview, removes rows that don't match the chosen regex, and highlights the filter icon in blue.

Include only by Regex

1. Click the filter icon next to the column name in the database preview or the ellipsis icon next to each field in the Header, Custom Column, or Dimension section, then choose Advanced Options.

2. In the 'Advanced Options' window, check the 'Include only by Regex' box and enter the relevant regex.

3. Click 'Create'.

4. This applies the filter in the database preview, keeps rows that match the chosen regex, and highlights the filter icon in blue.

Advanced Filters

1. Click the filter icon next to the column name in the database preview or the three dots ellipsis icon next to each field in the Header, Custom Column, or Dimension section, then choose Advanced options.

2. In the 'Advanced Options' window, click on 'Advanced Filter'.

3. Select a condition from the list and insert a value. You can add more conditions by clicking on 'Add condition'.

4. Click on 'Apply' and then click 'Create'.

5. This applies the filter in the database preview, keeps or removes rows that match the conditions, and highlights the filter icon in blue.

Q&A

Can I Filter a Lookup Column? 

Filters cannot be applied to Lookup columns, Table Attributes or Table IDs. To filter a Lookup column, you need to create an identical custom column by following these steps:

1. Click on 'Add Custom Column' under the Custom Columns Section.
2. In the 'Add Custom Column' window, enter a new field name. 
3. In the 'Fields' section, search for the relevant Lookup column name and double-click it to add it to the Formula bar. then click ok.
4. The new custom column will appear in the database preview, highlighted in yellow.
5. Click the filter icon next to the custom column to add any needed filters.

Why did the preview disappear after I applied a filter to one of the columns?

The database preview shows only approximately the first 200 rows of the data after mapping it and applying manipulations. If you apply a filter that starts from around row 201, the preview will appear empty. This happens because the data you are filtering falls outside the first 200 rows shown in the preview. However, the data is still in the database; it just can't be displayed in the current preview due to the row limit.

How can I use the Regex Filter?

Regex, short for "regular expression," is a way to create patterns for searching or filtering specific types of data. With regex, you can match things like dates, specific words, numbers, or patterns in text, making it easier to find exactly what you need.

Here are a few common examples:

• To filter for only 4-digit accounts in the Account ID column, use the regex pattern \b\d{4}\b.

• To filter Account Name entries that start with "Sales" only, use the regex pattern ^Sales.

• To filter for Account ID values between 1000 and 4000, use the regex pattern \b(1000|[1-3]\d{3}|4000)\b.

Updated December 24, 2024 15:02