Navicat "is a database management tool that can create multiple connections for managing different types of databases such as MySQL, Redis, Oracle, PostgreSQL, SQLite, SQL Server, MariaDB, and MongoDB. It is compatible with GaussDB, OceanBase, as well as cloud databases such as Alibaba Cloud, Tencent Cloud, Huawei Cloud, Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, and MongoDB Atlas. It is used for creating, managing, and maintaining databases. The functionality of Navicat is sufficient to meet all the needs of professional developers, but it is also simple and easy to operate for beginners in database servers. The user interface (GUI) of Navicat is well-designed to create, organize, access, and share information in a secure and simple manner.

In many ways, field mapping is very similar to the transformation process in information technology (IT). The latter is to convert a value into a value through an algorithm, while field mapping only converts the value of one or more columns into the value of another column.

Sometimes field mapping can be observed in the field list clause of a SELECT query. For example, the 'Sum of Payments per Movie Category' query is the data source for the entire series, returning a list of movie categories and their total sales (or more specifically, rent). We can use CASE statements to make certain category names more descriptive, such as changing "Games" to "Video Games":

Create New Releases vs. Other Categories data source

Before designing any chart, we need a data source to obtain the necessary information. When you have some data sources, you may find that reusing existing data sources is much easier than creating a new one from scratch. In fact, the 'Rentals by Category' data source we used last time can meet the requirements very well.

We can easily copy any project in the Navicat BI workspace by selecting the project in the workspace and right clicking (or holding down the Control key in macOS) it, then selecting 'Copy Project Type' from the pop-up menu. Here, the menu item we need is' Copy Data Source ':

This will create a new data source named 'Rentals by Category 1'. To rename a new data source, click on the item once to select it, and then click again to activate editing mode. When the label turns into a text box and the project text is highlighted in blue, it indicates that the project is ready for editing:

将新数据源命名为“New Releases vs. Other Categories”。按回车键保存新名称：

Add mapping field

To add a new 'Mapping Field' in the data source, right-click on the 'name' field (or hold down the Control key on macOS and click), and then select 'New Mapping Field...' from the pop-up menu:

The 'New Mapping Field' dialog box will open. Firstly, rename the target field name to 'mapped_category_name'.

Next, we need to map the "New" category name to a more descriptive name. The operation is as follows:

1. Due to the one-to-one mapping between the "New" category and the new value, please select the "one-to-one" option from the "Mapping Method" drop-down menu.

2. Select 'New' as the 'source value'.

3. Enter 'New Release' as the 'Mapping Value'.

Now, we will repeat this process for 'null' values (i.e. movies without assigned categories).

1. Click the inverted triangle button next to 'Add', and then select 'Add one-on-one value...' from the pop-up menu.

2. In the "Add One to One Value" dialog box, select the checkbox next to the (NULL) value and enter "Uncategorized" for "Map Value".

   
   

   
   

   

   
   

3. Click the "OK" button to close the dialog box, and then add a new row to the "Mapping Fields" table.

Finally, select the "New Value" radio button in the "Other Values" tab and enter "Other Categories" so that all other values will be assigned to this overall category. At this point, the dialog box should look like the following:

Click 'OK' to close the dialog box. You should now be able to see the 'mapped_category_name' field in the data grid: