RaqForum 19 No.
1 Reply  •  441 View  • 
     ...

Filter Data by Multi-Condition

 

Example

The Excel file "emp.xlsx" is an employee table, and part of the data is as follows:

..

We may filter the data by multiple conditions of name (NAME), birthday (BIRTHDAY), and department (DEPT). For example, find the record with the name Ryan and a birthday greater than 1980-01-01, and the result is as follows:

..

Write SPL script:


 A
1 =if(name!="" &&   name,"NAME==name",true)
2 =if(dept!="" &&   dept,"DEPT==dept",true)
3 =if(bday!="" &&   bday,"BIRTHDAY>date(bday,\"yyyy/MM/dd\")",true)
4 =filter=[A1:A3].concat("&&")
5 =file("emp.xlsx").xlsimport@t().select(${filter})
6 =file("result.xlsx").xlsexport@t(A5)

 

The script parameters are set as follows:

 

..

A1 Spell the condition string for the name, and if the name parameter is a null value or an empty string, it will return true

 

A2 Spell the condition string for the department, and if the department parameter is a null value or an empty string, it will return true

 

A3 Spell the condition string for the birthday. If the birthday parameter is a null value or an empty string, it will return true

 

A4 Use && to concatenate these condition strings

 

A5 Read the employee table and filter the data

 

A6 Export the result to result.xlsx

 

 


 
 •  441 View  •