Multi-conditional assignment

1.1 Expected effect

Sometimes users need to assign different values to different fields at the same time.

Example 1: The school personnel type field assigns 12 and 21 to staff, 1 and 2 to students, and the rest to others, as shown in the following figure.

Example 2: Assign the values of "Nanjing" and "Wuxi" as "Central Area" and "Key Area", and other areas as "Other", if the value is "Central Area", the value will be "A", "Key Area" will be "B", and "Other" will be "C", as shown in the figure below.

1.2 Implementation idea

This is achieved using the Switch function and IF function.

Example data: group assignment.xlsx

Upload the Excel data set to FineBI as shown below.

2.1 Create Self-Service dataset

Create the Self-Service dataset, select the Excel dataset that has been uploaded, and check the sample fields as shown below.

2.2 Multi-conditional assignment

Click "+" to add "New Column", as shown in the following figure.

2.2.1 Method 1

Name the new column "Multi-Conditional Assignment", enter the formulaIF(OR(type="12",type="21"), "staff", IF(OR(type="1",type="2"), "student", "other")), and click "OK", as shown in the following figure.

Formula Description.

2.2.2 Method 2

Name the new column "switch assignment" and enter the formula:SWITCH(type,"12","staff","21","staff","1","student","2","student"), click "OK", as shown below.

Add "New column" again, and assign the other data that does not belong to "switch assignment" to "other", enter the formula: IF(switch assignment = NULL, "other", switch assignment), as shown below.

2.3 Effect view

See Section 1.1 of this document for details.

Example using "Sales DEMO>Customer Dimension Table" data.

3.1 Create Self-Service dataset

Create the Self-Service dataset and select the "CustomerID""Province" "City"  fields under the "Customer Dimension Table" as shown below.

and filter out the data from Jiangsu Province as shown in the following figure.

3.2 Multi-conditional assignment

Click "+" to add "New Column", as shown in the following figure.

Name the new column "Multi-Condition Assignment", enter the formula:IF(Province="Jiangsu province",IF(City="Nanjing city", "central area",IF(City="Wuxi city", "key area", "other"), "other") , "other"), click "OK", as shown in the following figure.

Create the new column again, name the new column "Assignment", use the SWITCH function to assign the fields that meet the corresponding conditions, enter the formula: SWITCH(multi-conditional assignment, "central area", "A", "key area", "B", "other", "C") , and click "OK", as shown in the figure below.

You can continue with other operations.

3.3 Effect view

See Section 1.1 of this document for details.