How to Judge Equality and Belongingness Between Sets Regardless of Order

 

Examples

Example 1: Equality of sets

There is an Excel file Book1.xlsx, and the data is as follows:

code

order

A

1

A

2

A

3

A

4

B

1

B

2

B

4

B

3

C

4

C

2

C

3

C

1

There is an Excel file standard2.xlsx, and the data is as follows:

order

1

2

The task is to select all the data whose order value in Book1 is also the order value in standard2.

Write SPL script:


A

1

=file("Book1.xlsx").xlsimport@t()

2

=file("standard2.xlsx").xlsimport@t()

3

=A2.(order)

4

=A1.select(A3.pos(order))

A1 Read the Excel data 

A2 Read the Excel data 

A3 The order data in standard2 

A4 The pos function judges whether the order in Book1 belongs to A3, and filters the data according to the result of judgment. The results are as follows:

code

order

A

1

A

2

B

1

B

2

C

2

C

1

 

Example 2: Belonging to or containing

There is an Excel file Book1.xlsx, and the data is as follows:

code

order

A

1

A

3

A

5

A

7

B

2

B

4

B

6

B

8

C

1

C

2

C

3

C

4

There is an Excel file standard2.xlsx, and the data is as follows:

order

1

2

For the same code in Book1, select all the data where the order column contains all the code data of the order value in standard2.

Write SPL script: 


A

1

=file("Book1.xlsx").xlsimport@t()

2

=file("standard2.xlsx").xlsimport@t()

3

=A2.(order)

4

=A1.group(code).select(A3^~.(order)==A3).conj()

A1 Read the Excel data 

A2 Read the Excel data 

A3 The order data in standard2 

A4 Group the data by code, select those groups whose order contains the order value in standard2, merge them, and the results are as follows: 

code

order

C

1

C

2

C

3

C

4