Skillfully using IF function to process data

1. Overview

In data analysis, users often use IF judgment conditions to clean and process the existing data.

2. Handling special values

2.1 Example 1: the field value is empty

When processing data, you may encounter the situation that the field is empty. At this time, if you need to calculate or process the formula of non-0 data on this basis, you need to only process the non-empty data. You can use the IF function to judge.

Sample data:

404.Internet Access Statistics.xlsx

For example, in the "Internet Access Statistics", there is empty data in the "Statistical date" in the access statistics table. It is necessary to process the non empty "Statistical date" data as ten days after the current time. Since the null value will be processed as year 1970, it is necessary to nest a function combining IF and ISNULL outside the formula to ensure the correctness of the processed results.

Create a new self-service dataset, create a "new column" and use the formula  IF(ISNULL(Statistical date)=1,null,DATEDELTA(Statistical date,10)), as shown in the following figure:

Note: the DATEDELTA function can return the specific date after the specified date.

2.2 Example 2: ensure the correctness of division results

If the field needs to be divided, and the dividend or divisor is 0, you can also use the IF function.

For more details, see file: "Infinity / negative number / null value is displayed as 0".

2. Condition assignment

Users need to classify and assign data according to some conditions. At this time, IF function is a better choice.

Usually, IF function is nested with AND, OR, FIND, etc.

2.1 Example 1: nested use of multiple IF

Use the built-in dataset "FRDemo_ProfitAnalysis".

If the "Item" column contains the "income" field, it is equivalent to "Income", if contains the "expense" field, it is equivalent to "Expense", otherwise it is "Other".

Create a self-service dataset, create a new column and enter the formula: IF(FIND("income",${Item})!=0,"Income",IF(FIND("expense",${Item})!=0,"Expense","Other")), as shown in the figure below:

Note: fields need to be selected from the left side of the formula box and cannot be entered manually.

Formula description:

2.2 Example 2: use with logical functions

The built-in data set is still used. If one of "Current period > 1000" and "Category" belongs to "Expenditure" is satisfied, it is assigned as "specified contract", otherwise it is other.

Input formula:

IF(OR(${Current period}>1000,FIND("Expenditure",${Category})!=0),"Designated expense","Others") , as shown in the figure below:

Formula description:

IF(${Current period}>1000||FIND("Expenditure",${Category})!=0,"Designated expense","Others")

3. Processing logic error values

During data processing, users often encounter the problem of "logical value error" of data. For example, there is a data table with negative total dwell time. It is obvious that these data are wrong. At this time, data processing needs to be carried out to change those with negative total residencel time to "Error" for identification.

3.1 Operation steps

Create a new self-service dataset, create a "New column" and use the formula:

IF(${Total residence time}>=0,${Total residence time},"Error"), as shown in the following figure:

Note 1: the "Total residence time" field needs to be added into the formula box by clicking from the field selection box on the left.

Note 2: since "Error" is text data, double quotation marks are required.

3.2 View the effect