Extracting Data from One of Two Time Columns

Application Scenarios

Scenario description: In the sample data Resume Table, some employees only filled in their middle school graduation time, some only filled in their university graduation time, and others filled in both. Due to employees' different educational backgrounds, their final graduation time cannot be displayed in one column. To solve this problem, you can extract employees' final graduation time from data of multiple columns.

Example: The following table contains two raw data columns, A and B, and you need to add a column C. Column condition: Returns the values of B when A is empty; returns the values of A when B is empty; returns the maximum values of A and B when they are both not empty.

Implementation Method

In FineBI, you can use the IF and ISNULL functions to determine whether a field is empty and use the DATESUBDATE function to return the maximum values of A and B. The following document introduces the procedures of the above example in detail.

Adding Data

1. Download sample data: Resume Table.xls

2. Create an analysis subject and upload the sample data with all fields selected by default.

3. The preview of the uploaded data is shown in the following figure.

Editing Data

1. Add a Formula Column named C and enter the formula IF(ISNULL(A),B,IF(ISNULL(B),A,IF(DATESUBDATE(A,B,"d")>0,A,B))). The IF, ISNULL, and DATESUBDATE functions are used to respectively determine the condition, determine whether a field is empty, and compare the values of two time fields.

Note:

The time fields A and B need to be selected from the left column and cannot be entered manually.

2. Click OK and the added column C is displayed in the preview area, as shown in the following figure.

Demonstration

Click Save All and Update and then click  to return to My Analysis. You can see the added column C in the automatically updated Data Preview tab.