In data analysis, users often use IF judgment conditions to clean and process the existing data.
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.
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".
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.
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:
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:
IF(${Current period}>1000||FIND("Expenditure",${Category})!=0,"Designated expense","Others")
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.
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.
滑鼠選中內容,快速回饋問題
滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy