Using Conditional Branch to Obtain Information About Outstanding Employees

Application Scenario

The company calculates the total sales volume of employees every month. If the total sales volume is greater than or equal to 500, the company filters out the information of employees with sales volume above the average, writes their information into the outstanding employee table, and praises and rewards these employees.

If the total sales volume is less than 500, the company filters out employees with sales volume below the average, writes their information into the employee needing improvement table, and arranges for regional managers to communicate with them to support their development.

Implementation Method

Use a Parameter Assignment node to obtain the total sales volume of employees and pass it to a parameter.

Use a Conditional Branch node to judge the parameter value. If the value is greater than or equal to 500, execute the downstream node A; if it is less than 500, execute the downstream node B.

Creating a Database Table

Create a scheduled task, drag an SQL Script node onto the design page, and rename it to Creating an Employee Sales Table.

The SQL statement is used to create an employee sales table named Sales in the database, with ID as the primary key.

The SQL statement is as follows:

CREATE TABLE `demo1`.`Sales` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Sales` decimal(10,2) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `Province` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

If the above SQL statement throws an error, try the following statement:

CREATE TABLE demo1.Sales (  
    ID int(11) NOT NULL AUTO_INCREMENT,  
    Name VARCHAR(255) DEFAULT NULL,  
    Sales DECIMAL(10,2) DEFAULT NULL,  
    City VARCHAR(100) DEFAULT NULL,  
    Province VARCHAR(100) DEFAULT NULL,  
    PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Inserting Data into the Table

Drag another SQL Script node onto the design page, rename it to Inserting Data into the Employee Sales Table, and connect it to the upstream Creating an Employee Sales Table node.

The SQL statement is used to insert demo data into the created table.

The following figure shows the table structure.

The SQL statement is as follows:

INSERT INTO `demo1`.`Sales` (`ID`, `Name`, `Sales`, `City`, `Province`) 
VALUES 
  (NULL, 'Oliver Zhang', 45.00, 'Suzhou', 'Jiangsu'),
  (NULL, 'Liam Li', 50.00, 'Wuxi', 'Jiangsu'),
  (NULL, 'Noah Wang', 70.00, 'Changzhou', 'Jiangsu'),
  (NULL, 'Lucas Liu', 30.00, 'Wuxi', 'Jiangsu'),
  (NULL, 'Ethan Cao', 80.00, 'Suzhou', 'Jiangsu'),
  (NULL, 'Mason Zhang', 60.00, 'Changzhou', 'Jiangsu'),
  (NULL, 'Logan Wang', 40.00, 'Wuxi', 'Jiangsu'),
  (NULL, 'Jacob Chen', 100.00, 'Changzhou', 'Jiangsu'),
  (NULL, 'Elijah Zhou', 50.00, 'Nanjing', 'Jiangsu'),
  (NULL, 'James Liu', 75.00, 'Nanjing', 'Jiangsu');

Obtaining the Total Sales Volume

1. Drag a Parameter Assignment node onto the design page, rename it to Obtaining the Total Sales of All Employees, and connect it to the upstream Inserting Data into the Employee Sales Table node.

Configure the data source of the node. The SQL statement is used to calculate the total sales volume by summing all sales records  in the Sales table.

The SQL statement is as follows:

SELECT sum(Sales) as Sales From `demo1`.`Sales`

2. Set the obtained total sales volume as the output parameter for the downstream Conditional Branching node to determine whether to write employee information to the specified table, as shown in the following figure.

Storing Information on Outstanding Employees

1. Drag a Data Synchronization node onto the design page and rename it to Storing Information on Employees Whose Sales is Greater than or Equal to the Average.

Set the data source of the node. The SQL statement is used to filter the data of the employees whose sales volume is greater than or equal to the average from the Sales table.

The SQL statement is as follows:

SELECT
    Sales.* 
FROM
    (SELECT Province, AVG(Sales) AS Average_Sales FROM `demo1`.`Sales` GROUP BY Province) AS Province_Average
    LEFT JOIN `demo1`.`Sales` ON Province_Average.Province = Sales.Province
WHERE
    Sales.Sales >= Province_Average.Average_Sales

2. Set the data destination to the Good_User table to store the data of the employees whose sales volume is greater than or equal to the average. You can keep the default setting of Field mapping.

Storing Information on Employees Needing Improvement

1. Drag a Data Synchronization node onto the design page and rename it to Storing Information on Employees Whose Sales is Less than the Average.

Set the data source of the node. The  SQL statement is used to filter the data of the employees whose sales volume is less than the average.

The SQL statement is as follows:

SELECT
    Sales.* 
FROM
    (SELECT Province, AVG(Sales) AS Average_Sales FROM `demo1`.`Sales` GROUP BY Province) AS Province_Average
    LEFT JOIN `demo1`.`Sales` ON Province_Average.Province = Sales.Province
WHERE
    Sales.Sales < Province_Average.Average_Sales

2. Set the data destination to the Reminder_User table to store the data of the employees whose sales volume is less than the average.. You can keep the default setting of Field mapping.

Setting Judgment Conditions for Execution

1. Drag a Conditional Branch node onto the design page and rename it to Judging Whether the Total Sales Volume is Greater than or Equal to 500.

Connect the Conditional Branch node to the upstream  Obtaining the Total Sales of All Employees node, the downstream Storing Information on Employees Whose Sales is Greater than or Equal to the Average node, and the downstream Storing Information on Employees Whose Sales is Less than the Average node.

Click the Conditional Branch node and two downstream nodes are displayed. You can set judgment conditions for the two nodes to determine which one will be executed  when the task runs.

2. Set judgment conditions for the two nodes, as shown in the following figure.

Running the Task

Because the value of the Sales parameter is 600, the Storing Information on Employees Whose Sales is Greater than or Equal to the Average node is executed, while the Storing Information on Employees Whose Sales is Less than the Average node is skipped.

In the Good_User table, which is the target table set in the Storing Information on Employees Whose Sales is Greater than or Equal to the Average node, you can view the written data, as shown in the following figure.