Take one of the two time data columns

1.Description

Scenario description: In the personnel resume table, there is possibility that employees may fill in the table casually. For example, some people fill in the time of graduation from high school, while some others fill in the time of graduation from university. Considering employees have different educational backgrounds, it is impossible to judge the employee's last graduation time through one column of data. Therefore, it is necessary to obtain the employee's graduation time from multiple columns of data.

Application example: For example, the original data in the figure below contains two columns of data as A and B, and a new column of data C needs to be added. The acquisition condition of C is that when A is null, you get B; when B is null, you get A; when neither A nor B is null , you get the maximum value.

How to achieve: In FineBI, you can currently use the IF function to make this scenario come true, that is, if A is null, take B. If B is null, take A. If neither A nor B is null, take the value according to the value of A and B. Below we introduce in details the steps to implement the above example in FineBI.

2. Operation steps

2.1 Add data

In the business package, select "Add Table>EXCEL Dataset", and import the original data including columns A and B into the business package, as shown in the figure below .

The preview of the successfully imported data is as follows:

2.2 Create self-service dataset

On the business package editing page, select "Add Table> Self-Service Dataset", as shown in the figure below:

Add fields A and B from the imported table, and select "New column" in the step column on the left, as shown in the figure below:

Name the new column as C. The formula to get the new column is as shown in the figure below, which is if(isnull(A),B,if(isnull(B),A,if(DATESUBDATE(A,B,"d")>0, A,B))). The functions used are if, isnull, and DATESUBDATE, which are used to determine conditions, determine whether the value is null, and compare date, respectively.

Note: The time fields A and B in the formula cannot be input directly. You need to select fields.

Click "OK" to save the new column, and the new column C will appear in the data preview, as shown in the figure below:

2.3 View  effect

Right now, we click "Save" in the upper right corner to save the self-service dataset, and click "Update Progress" on the business package interface. After the display is completed, enter the data preview, and you can see the self-service dataset contains the newly added column C.